Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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.
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
User | Count |
---|---|
106 | |
85 | |
81 | |
73 | |
71 |
User | Count |
---|---|
111 | |
102 | |
97 | |
74 | |
67 |