Power BI - Decomposition tree values and percentages

[Anonymous]

I'm using decomposition tree and I want so show off both the value and the percentage. Is it possible?

Additionally, I'm branching it based on a measure, then country, then manufacturer etc. For the percentages, lets say I have manufacturer A with 75 products mark C, and manufacturer B with 25 products mark C. Total si 100 products mark C and I want percentages based on that for every column I have.

[Anonymous · Accepted solution]

Hi @Anonymous 

Please try this:

Here I create a set of sample:

Then add a measure:

MEASURE =
VAR _currentMark =
    SELECTEDVALUE ( 'Table'[product mark] )
VAR _result =
    SUM ( 'Table'[Revenue] )
        / SUMX (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[product mark] = _currentMark ),
            'Table'[Revenue]
        )
RETURN
    IF (
        HASONEVALUE ( 'Table'[product mark] ),
        _result,
        SUM ( 'Table'[Revenue] ) / SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Revenue] )
    )

 The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Khushboobarai]

but its t still howing only the % values?? how can i proceed if i wnat both ? pls help

[rohit1991]

Hi @Anonymous ,

Since the Decomposition Tree does not natively support showing both the absolute values and percentages, you can achieve this by creating custom DAX measures that calculate and display them together.

1. Create a Measure for Absolute Values: Total_Products = SUM('Table'[Product_Count])

2. Create a Percentage Measure

Since percentages should be calculated at each level dynamically, use the following measure:

Percentage_of_Total = 
VAR CurrentValue = SUM('Table'[Product_Count])
VAR TotalValue = CALCULATE(SUM('Table'[Product_Count]), ALL('Table'))
RETURN 
    DIVIDE(CurrentValue, TotalValue, 0)

3. Combine Value and Percentage into a Single Measure

Since the Decomposition Tree only allows one numeric value, concatenate the value and percentage into a string:

Value_Percentage = 
VAR CurrentValue = SUM('Table'[Product_Count])
VAR ParentValue = CALCULATE(SUM('Table'[Product_Count]), ALLEXCEPT('Table', 'Table'[Manufacturer]))
VAR Percentage = DIVIDE(CurrentValue, ParentValue, 0)
RETURN 
    FORMAT(CurrentValue, "#,##0") & " (" & FORMAT(Percentage, "0%") & ")"

4. Add the Measure to the Decomposition Tree

1. Insert a Decomposition Tree visual in Power BI.
2. Drag the Value_Percentage measure into the Analyze field.
3. Add Country, Manufacturer, and Product Mark to the Explain by field.
4. The values will now appear as "Value (Percentage)" in the breakdown.

[Anonymous]

The "Analyze" field requires a number, I can't put a string value inside.

[anilelmastasi]

Hi @Anonymous ,

Unfortunately, Power BI’s Decomposition Tree visual does not support displaying both value and percentage together by default. However, there is a workaround to achieve this. Since the Decomposition Tree only allows showing a single numeric value, you can create a measure that concatenates both the actual value and the percentage as a text label.

For example:

TotalProducts = CALCULATE(SUM(YourTable[Product Count]), ALL(YourTable)) 

And Percentage Calculation:

Percentage = 
VAR CurrentCount = SUM(YourTable[Product Count])
VAR TotalCount = [TotalProducts]
RETURN
DIVIDE(CurrentCount, TotalCount, 0)

And Combine these values:

ValueAndPercentage = 
VAR CurrentCount = SUM(YourTable[Product Count])
VAR Percent = FORMAT([Percentage], "0.0%")
RETURN
CurrentCount & " (" & Percent & ")"

If that works for you, accept as a solution please.

Thank you!

[Khushboobarai]

Hii, could you pls help me, i tried you rversion for combining both th evalue and percentange , but isnt that measure gonna be a text format... so how can i put into that analyse?

[Anonymous]

Is it dynamic, will the branching work then? Also, I have 5 categories, not just product, will it work for others?
Also, I have created a measure that combines value and percentage and I can't put it in "Analyze" because now it is a "text" and not a number.