Calculation at a specific level in a hierarchy

[Anonymous]

I want to be able to calculate an absolute variance between Forecast and Actuals but have the calculation fixed at specific hierarchy levels so when I view it at a higher level it is the sum of the lower level calculations and not claculated at the higher level.  For example if I need to calculate Part/Customer/Country how do I fix it at that level.  Clearly my formula is too basic, 

AbsVar = ABS(CALCULATE([Dmd Qty]-[Act Qty])).  Do I use the SELECTVALUE function to force the calculation at a specific level?

[Anonymous]

Hi @Anonymous ,

These two functions are often used in the calculation of hierarchical relationships.

ISFILTERED – DAX Guide

DAX ISINSCOPE create dynamic drilldowns in Power BI | Microsoft Power BI (medium.com)

With the SWITCH function, you can write different values that need to be output at different levels.

Best Regards,

Neeko Tang

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

[Anonymous]

Thanks Neeko.  Yes this does work and I have done something very similar.  

AbsVar(Part) = SUMX(VALUES(PartSite[Part]),[MainForecastActual]) but will try it with your ISFILTERED suggestion.  This is partly about how to display the correct data at a summarised level without showing the detailed level.  I will share some data once I get chance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create measures.

AbsVar = ABS(SUM('Table'[Forecast])-SUM('Table'[Actuals]))

AbsVar2 = IF(ISFILTERED('Table'[Part]),[AbsVar],SUMX(VALUES('Table'[Part]),[AbsVar]))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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