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

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!

11 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ，

     

    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:

     

     

    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!
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • 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

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey Anonymous 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...

  • A_richy's avatar
    A_richy
    Frequent Visitor
    5 years ago

    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

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


  • xexo93's avatar
    xexo93
    New Member
    1 year ago

    Hello, to get the percentage, go to analyze section and then open the options, then go at the end to 'show values as', then select 'percent of grand total'

     

    Hope this helps!