cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jaap_Olsthoorn
Post Patron
Post Patron

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:

Decomp tree.png

 

You see here that the percentages in plaza are based on the total, which is set to 100%. The categories however, are also based on the first measure, so when you add up all the category percentages, you would get the plaza percentage, instead of 100%

 

Does anyone know if it is possible to make every step in the tree use 100% as it's base? Maybe using a particular DAX measure?

 

All thelp is appreciated!

 

Jaap

1 ACCEPTED SOLUTION

Hi @Jaap_Olsthoorn ,

 

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":

Annotation 2020-03-27 092035.png

 

Remember to set measure 2 as percentage:

Annotation 2020-03-27 092303.png

 

Finally you will see:

 

Annotation 2020-03-27 092134.png

 

For the related .pbix file,pls click here.

 

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

7 REPLIES 7
A_richy
Frequent Visitor

Hi @v-kelly-msft 

 

Great solution . Can you help me with writing a measure using the If condition and the Hasonefilter functions to create the dax measure for 4 EXPLAINED VARIABLES? Thank you

manishak
New Member

manishak_0-1596572843942.png

referring the example from your question  can you tell me how you are able to show the exact % value . as if I have data as 70% always on decomposition tree it shows as 0.7. I want it to appear as 70% not happening. can anyone give me solution. I have already set the data type as decimal nd formating as percentage.

Hey @manishak I think all I did was use the "Show value as" -> "Percent of grand total" when clicking the drop down on your measure. Not sure if that will work for what you are creating...

v-kelly-msft
Community Support
Community Support

Hi @Jaap_Olsthoorn

 

Based on my research, it cant be realized currently. Logically,"category" is the child node of "plaza",so the total should be the plaza percentage.

 

Here I have a workaround for you:

Create 2 calculated columns using  dax expressions such as below:

combine = 'Table'[Plaza]&"-"&'Table'[category]
Percentage = FORMAT(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[combine]))/CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Plaza])),"percent")

And you will see:

 

Annotation 2020-03-26 151649.png

 

The value I marked in red line is the one you wanna get.

 

Here is a sample data you can refer to.

 

If the workaround cant fulfill what you need, you can come up with a new idea and add your comments there to make this feature coming sooner.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

Best Regards,
Kelly
Did 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 @Jaap_Olsthoorn ,

 

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":

Annotation 2020-03-27 092035.png

 

Remember to set measure 2 as percentage:

Annotation 2020-03-27 092303.png

 

Finally you will see:

 

Annotation 2020-03-27 092134.png

 

For the related .pbix file,pls click here.

 

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.