Creating list report across years.

[asmfloyd]

Hello,
  I am trying to create a list report where I need to divide columns A/B (data rolled up from detail for each columns) to get a result across years.

Looking to have
                      DIM 2023   2024   2025
 
                       A      6.2   7.3    5.7

To achieve this, For 2023, I am currently creating 3 variables (query calculation) and is working. Var3 is the result.
   Var 1--If (year=2023) THEN (A) ELSE (NULL)
   Var 2--If (year=2023) THEN (B) ELSE (NULL)
   Var 3--(Var1/Var2)*100

If I try to do these in single variable as below, it is not giving the intended result. Can I know what I am doing incorrect?
    If (year=2023) THEN (A/B) ELSE (NULL)
     I am keeping Detail aggregation as Total.

Thanks,

[dougp]

Assuming that the Detail Aggregation is Total for Var 1, Var 2, and Var 3, my guess is the one that is working is basically being interpreted as

Code Select Expand

Var 3 = (total(If (year=2023) THEN (A) ELSE (NULL))/total(If (year=2023) THEN (B) ELSE (NULL))*100
So when you try

Code Select Expand

Var 3 = If (year=2023) THEN (A/B) ELSE (NULL)it's performing the calculation before aggregating A and B.  And since NULL/B and A/NULL and NULL/NULL all result in NULL, and this is before the values are being aggregated, you will get different results than if you aggregate before calculating.

You can check this by clicking the 3 dots in the top right (next to Properties) and using Show Generated SQL/MDX.  That will enable you to inspect the SQL to see what's really going on.