% of Parent Row Total for Multi-level Hierarchy

[khizerdaar]

Hi,

I'd like to preface by saying I have tried looking for a solution to my case on the forum - I found two potential solutions, implemented both, neither worked completely for my case. The problem is the lack of a "show values as % of Parent Row Total for a multi-level hierarchy". 

Details for the below attached case:

Source Table Name: 'All Customer Files'

Source Table Values: 'All Customer Files' [Value]

Four hierarchies from the table in this order: [Category Name], [Subcategory Name], [Form], [Brand Name]

Four custom measures: Current Month, Last Month, P6M and vs P6M

Has anyone had success in a case like this and if so what code did they use?

[Chirag4370]

Hello @JustJan 

Thanks for this solution. It's working 99% for me. but one problrm is there. Let's say We have 5 Continent. If we select only two continent using slicer/Filter. though given DAX code consider Sum of all 5 continent as denominator. because we use All() function.

letter on, I tried uasing ALLSELECTED(), and it works 100% for me. 

[JustJan · Accepted solution]

Hi @khizerdaar 

Maybe this can help you: 

there are a number of measures here. (In the visual they are  just there to check the result)

The measures are: 

% State =
divide([Sales Amount], CALCULATE([Sales Amount], ALL('Customer'[State])))

% Country =
divide([Sales Amount], CALCULATE([Sales Amount], ALL('Customer'[CountryRegion], Customer[State])))

% Continent =
divide([Sales Amount], CALCULATE([Sales Amount], ALL('Customer')))

Row Level = 
Switch (
   true(),
   ISFILTERED(Customer[State]), "State",
   ISFILTERED(Customer[CountryRegion]), "Country",
   ISFILTERED(Customer[Continent]), "Continent",
   NOT ISFILTERED(Customer[Continent]), "Overall"
)

% ParentRow = 
Switch (
   true(),
   ISFILTERED(Customer[State]), [% State],
   ISFILTERED(Customer[CountryRegion]), [% Country],
   ISFILTERED(Customer[Continent]), [% Continent],
   NOT ISFILTERED(Customer[Continent]), [% Continent]
   )

It should not be too much of a problem to translate it to your own model.

Hope this helps (completely 🙂 )

Jan