Change the measure result if I change the explain by categories decomposition tree

[sperry]

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.

Example

[littlemojopuppy]

@sperry what do you mean by "if I want to show values of the end 'explain by' category first"?

[sperry]

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.

[sperry]

and this is the measure;

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),

1

)

[littlemojopuppy]

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?

[sperry]

Yeah - unfortunately tried that and it breaks the top of heirarchy;

[littlemojopuppy]

😫  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

[sperry]

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.

[littlemojopuppy]

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 🙂

[sperry]

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 .

[littlemojopuppy]

Maybe I could help after all 🙂

[sperry · Accepted 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]
)

[littlemojopuppy]

Woohoo!!!  Mark yours as the solution!  🙂

[sperry]

will do - hmmm to kudos or not to kudos 🙂