Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |