Forum Discussion
Decomposition tree percentages based on first value
Hi Anonymous ,
I find a solution for you:
Instead of creating calculated columns,you can create a measure:
Measure 2 = IF ( HASONEFILTER ( 'Table'[category] ), COUNT ( 'Table'[measure] ) / CALCULATE ( COUNT ( 'Table'[measure] ), ALLEXCEPT ( 'Table', 'Table'[Plaza] ) ), COUNT ( 'Table'[measure] ) / CALCULATE ( COUNT ( 'Table'[measure] ), ALL ( 'Table' ) ) )Put measure 2 in the "Analyze" filed, and "plaza" and "category" in the "explain by":
Remember to set measure 2 as percentage:
Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Thanks a lot for thinking along Kelly,
your solution definitely works for my example, but as always, my example is just a fraction of what the report does. I actually have 5 different "explain by" factors that the users can choose, so that would lead me to need 5! of these combination columns. And users should be able to use the decomposition tree freely, so that they can choose their own order (so category to plaza as well as plaza to category).
I think this is just not possible currently, but I wouldn't be surprised if this is still in development.
Again, thanks a lot for your assistance!
Jaap
Hi Anonymous ,
I find a solution for you:
Instead of creating calculated columns,you can create a measure:
Measure 2 =
IF (
HASONEFILTER ( 'Table'[category] ),
COUNT ( 'Table'[measure] )
/ CALCULATE ( COUNT ( 'Table'[measure] ), ALLEXCEPT ( 'Table', 'Table'[Plaza] ) ),
COUNT ( 'Table'[measure] )
/ CALCULATE ( COUNT ( 'Table'[measure] ), ALL ( 'Table' ) )
)
Put measure 2 in the "Analyze" filed, and "plaza" and "category" in the "explain by":
Remember to set measure 2 as percentage:
Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hey Kelly,
Really nice solution! Unfortunately like I said earlier, in the decomposition tree, one of the cool things is that your end users can determine what order to put the categories in. So they can choose to just take category first, and then plaza. In that case your measure, which works really well in this scenario, would not work in other scenario's.
I'll still accept your solution, because it does work for the example I gave you 🙂
Jaap
what is [measure]? the one inside that Measure 2 formula is referencing?
