How to calculate a hierarchy level in Dax?

Question

I need some help with a dax measure. Below you see a matrix in Power BI Desktop. What I want is to calculate the ratio of the Sum(of 0 and 1 level) divided by the measure [Total Salg vekt], which I already has calculated. But I am struggling getting the sum of the second level of my product hierarchy. I don't want to calculate level 1 or 3 in the hierarchy.

My hierarchy is located in my Product table and is named like this

Products - Category - Code - SubCategory

Can any of you show a dax code that my work as described above?

Thanks

Regards Geir

I'm not completely clear on what you're trying to do. Can you mock up what you want the result to look like? — Alexis Olson
I am sorry that I was not clear enough. I want to Sum the second level 281362+180672 and divide it with the total 737357 (sum of all level). The value [Salg vekt kg] is only an ordinary SUM measure. When dragging on a hierarchy I got the values you see on the picture. The question is should I create a second measure where I set on a spesial filter. Hope I was clear now :-) — user5767413
Still not clear. Can you give an example of what you want your matrix to look like with this new measure? — Alexis Olson

Hila DG Hila DG · Accepted Answer · 2018-11-05T04:28:03

I have bad news:

There's no hierarchy in DAX. You'll have to use different filters in order to achieve what you want.

Complete explanation is in here: https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

And yes, it is not simple, but it answer your question.

How to calculate a hierarchy level in Dax?

1 Answers