Matrix Row Subtotal Random Text

[edwinb]

I'm using the Matrix visual with a text description. When I expand the visual +/- you can see the correct description on the actual row, but it shows a random description in the subtotal header from a sub-row below. The description is set to show the first description, but it doesn't always do this. Is there anyway to fix this behavior?

Thanks! 

[V-lianl-msft · Accepted solution]

Hi @edwinb ,

The reason for this issue is the default alphabetical order.

Please try the following steps:

1.Add an index column to the query editor.

2.Get the first "description" based on the index column

Measure =
VAR MIN_INDEX =
    CALCULATE (
        MIN ( 'Table (2)'[Index] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[level1] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table (2)'[level2] ),
        MAX ( 'Table (2)'[Description] ),
        CALCULATE (
            MAX ( 'Table (2)'[Description] ),
            FILTER ( 'Table (2)', 'Table (2)'[Index] = MIN_INDEX )
        )
    )

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

[edwinb]

@V-lianl-msft 

I keep trying to rework my power bi file with no luck. I'm not sure why I can't get the subtotal rows to show the correct text descriptions? Reguardless of what level is expanded, I'm trying to get the description to show correctly. It does so when there is only one option, but not for subtotals with more than one desciption. I'll keep trying, but if you have time to look at it, I provided a box link to the pbix file above. Thanks

Measure Description = 
VAR MIN_INDEX =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[L1] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[L3] ),
        MAX ( 'Table'[Description] ),
        CALCULATE (
            MAX ( 'Table'[Description] ),
            FILTER ( 'Table', 'Table'[Index] = MIN_INDEX )
        )
    )

 

[edwinb]

@V-lianl-msft Thanks for pointing me in the right direction.

Here is what I finally got working. I had to simplify the variable to work on the different levels of hierarchy

Measure Description = 
VAR MIN_INDEX =
            MIN ( 'Assembly Codes & Unit Costs'[Index] )
RETURN
    IF (
        ISINSCOPE ( 'Assembly Codes & Unit Costs'[L4] ),
        MAX ( 'Assembly Codes & Unit Costs'[Description] ),
        CALCULATE (
            MAX ( 'Assembly Codes & Unit Costs'[Description] ),
            FILTER ( 'Assembly Codes & Unit Costs', 'Assembly Codes & Unit Costs'[Index] = MIN_INDEX )
        )
    )

 

[Greg_Deckler]

@edwinb  Well, it is not random. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907