topic Re: Calculation at a specific level in a hierarchy in DAX Commands and Tips

Calculation at a specific level in a hierarchy

Anonymous — Mon, 19 Aug 2024 08:09:18 GMT

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?

Re: Calculation at a specific level in a hierarchy

Anonymous — Tue, 20 Aug 2024 02:24:35 GMT

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.

Re: Calculation at a specific level in a hierarchy

Anonymous — Tue, 20 Aug 2024 08:56:02 GMT

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.

Re: Calculation at a specific level in a hierarchy

Anonymous — Wed, 21 Aug 2024 06:06:10 GMT

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.