Forum Discussion

han_rj's avatar
han_rj
Helper IV
1 year ago

Nested Calculation item not working

I have created a calculation item for This Year and Prior Year and wanted to calculate Variance for it. But the varaince calculation item is not working  Variance = CALCULATE(SELECTEDMEASURE()...
han_rj's avatar
han_rj
Helper IV
1 year ago

Anonymous , Thank You for responding

 

This is the sample data

 

Calculation Items used

Last_Year = CALCULATE(SELECTEDMEASURE(),'Table'[Last_Year_Ind] = "Y")
This_Year = CALCULATE(SELECTEDMEASURE(),'Table'[This_Year_Ind] = "Y")
YOY = CALCULATE(SELECTEDMEASURE(),'Table'[This_Year_Ind] = "Y") -
CALCULATE(SELECTEDMEASURE(),'Table'[Last_Year_Ind] = "Y")
 
Current Results

Expected

I want variance for the current year only

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi han_rj 

 

Please try this:

First of all, Create a measure:

Measure = SUM('Table'[Sales])

Then add a calculation group:

Last_Year = CALCULATE(SELECTEDMEASURE(),'Table'[Last_Year_Ind] = "Y")
This_Year = CALCULATE(SELECTEDMEASURE(),'Table'[This_Year_Ind] = "Y")
YOY =
CALCULATE ( SELECTEDMEASURE (), 'Table'[This_Year_Ind] = "Y" )
    - CALCULATE ( SELECTEDMEASURE (), 'Table'[Last_Year_Ind] = "Y" )

Finally, create a Matrix and the result is as follow:

 

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • han_rj's avatar
    han_rj
    Helper IV
    1 year ago

    Thank You Anonymous for responding the format I am looking for is like this

    CY, TY label and the Year.

    If we exclude the Year in the column we get the correct results but when we add Year to the column the variance is off like this