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,
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
Var 3 = (total(If (year=2023) THEN (A) ELSE (NULL))/total(If (year=2023) THEN (B) ELSE (NULL))*100
So when you try
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.