Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Decomposition tree percentages based on first value

Hi all,   I'm struggling getting the decomposition tree to do what I want.  I'm currently looking at this example:   You see here that the percentages in plaza are based on the total, w...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
3 years ago

In case anyone stumble upon this thread in the future I'll provide my solution.

This guy gave also gave a solution aligned to the previous answers https://bielite.com/blog/calculating-percent-of-subtotal/ous answers, however, not being DAX savvy by any means, reading new formulas like inscope gave heart palpitations.
So what I ended up doing is a couple of pie charts / bar charts to explain these %, the user can now filter in the decomposition tree and see the % on the other visuals. Not the cleanest solution, not the most sophisticated solution, but knowing the audience these categories will change several times and hopefully this approach will remain clear regardless of the order / categories in place.

Cheers,