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.
Hello
I have found this solution to the calculating a % of subtotal for a decompostion tree but this doesn't work if I want to show values of the end 'explain by' category first. Here is the link to the example model.
Solved! Go to Solution.
Perfect..slight modification
Percentage by Category =
VAR CurrentRegion = VALUES(Combined[Region])
VAR CurrentDHB = VALUES(Combined[DHB_name])
VAR PopulationForCurrentSelection = SUM(Combined[Population])
VAR TotalPopulation =
SWITCH(
TRUE(),
ISINSCOPE(Combined[DHB_name]),CALCULATE(SUM(Combined[Population]),ALLSELECTED(Combined),Combined[Region] IN CurrentRegion),
ISINSCOPE(Combined[Region]),CALCULATE(SUM(Combined[Population]),ALLSELECTED(Combined))
)
RETURN
IF(
ISINSCOPE(Combined[Region]),
DIVIDE(PopulationForCurrentSelection,TotalPopulation),
[Percentage of NZ Population]
)
@sperry what do you mean by "if I want to show values of the end 'explain by' category first"?
The population model attached is broken down into subnational regions to 2 geographic levels, Region and District Health Board. The report entitled 'Decomposition Tree - Region & DHB' works as expected.
So the Midland Region is broken down by DHB by 'resetting' the parent to 100%.
The second report entitled 'Decomposition Tree - DHB only' shows what happens if Region is removed or not included;
I need a way of making the DHB level use the 'Total Population' as the parent and denominator, sum each DHB population as the numerator to provide a DHB as a % of the total population.
and this is the measure;
Hi @sperry
Your measure has this at the end...that's why it's reporting 100% for everything. What if you delete the stuff in green?
Yeah - unfortunately tried that and it breaks the top of heirarchy;
😫 dammit.
Sorry...I'm not very familiar with that visualization. If I tried to help, I'd probably end up screwing it up more on you
haha all good..yup - frustrating me as I know that the second clause in the if statement is what's breaking it - just don't know how to resolve.
Question...how should it be calculated?
Reason I'm asking is that maybe a solution could be to create a measure for what you have now (removing the conditional). Another measure for how it should be calculated with the different level. And then a third measure that you'll actually put in the report that's essentially
SWITCH(
TRUE(),
ISINSCOPE(Combined[Region]), [Region Measure],
ISINSCOPE(Combined[DHB_name], [A Different Measure],
(continue as necessary)
)
If nothing else, it would simplify having to maintain the individual calculations 🙂
Ooo I'l give that a go - hadn't thought of that. It should be using the category 'DHB_name' to divide the DHB population by the total population .
Maybe I could help after all 🙂
Perfect..slight modification
Percentage by Category =
VAR CurrentRegion = VALUES(Combined[Region])
VAR CurrentDHB = VALUES(Combined[DHB_name])
VAR PopulationForCurrentSelection = SUM(Combined[Population])
VAR TotalPopulation =
SWITCH(
TRUE(),
ISINSCOPE(Combined[DHB_name]),CALCULATE(SUM(Combined[Population]),ALLSELECTED(Combined),Combined[Region] IN CurrentRegion),
ISINSCOPE(Combined[Region]),CALCULATE(SUM(Combined[Population]),ALLSELECTED(Combined))
)
RETURN
IF(
ISINSCOPE(Combined[Region]),
DIVIDE(PopulationForCurrentSelection,TotalPopulation),
[Percentage of NZ Population]
)
Woohoo!!! Mark yours as the solution! 🙂
will do - hmmm to kudos or not to kudos 🙂
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |