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!

v-kelly-msft's avatar
v-kelly-msft
Community Support
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
6 years ago

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