Urgent support - Matrix adjust row subtotal and total

[xiumi_hou]

Hi Everyone,

I have a matrix with 5 level rows and several columns. Each row has a row level total. Can I adjust two row level, in the graph below, I would like to make the first sub-total in orange, the same with total. And hide the 2nd sub total. Please note that those two sub total at the aggregation of the same level 

[danextian]

Hi @xiumi_hou 

What you're trying to achieve is partially possible:

• conditional formatting cannot be applied to row and column headers. You can apply formatting to row and column headers but they will not be conditional - they will be applied to all.
• you can make the subtotal line disappear  if set to top. You screenshot shows they're at the bottom

Here's a sample formula for background conditional formattting to be applied as a field value to both values and totals. You will need to specify in the measure the first subtotal column value (APAC in my example).

Total Color = 
IF (
    NOT ( HASONEVALUE ( Geo[Geo] ) )
        || (
            NOT ( HASONEVALUE ( Category[Category] ) )
                && SELECTEDVALUE ( Data[Geo] ) = "APAC"
        ),
    "orange"
)

The formula below will selectively hide the subtotal. You will need to specify the second subtotal value which in my example is EMEA.  This will check whether the category value is EMEA and the lower level hierarchy is not in scope as the one that is inscope at the subtotal level is EMEA. If the condition is SELECTEDVALUE ( Data[Geo] ) = "EMEA", it will return blank for all rows which geo is EMEA.

Total_Revenue = 
IF (
    SELECTEDVALUE ( Data[Geo] ) = "EMEA"
        && NOT ( ISINSCOPE ( Category[Category] ) ),
    BLANK (),
    [Total Revenue]
)

Please see attached sample pbix.

[xiumi_hou]

@danextian  Thank you for the detailed reply. So some adjust is needed:

1.For the sub total color, i also want to color to cover the row header based on the screenshot I provided.

2.I can not make the sub total at the top because the sub total need to be at the bottom based on the original design of the matrix, and and I want the sub total to be totally disappear.

Can you help me take a look for that?

[danextian]

Hi @xiumi_hou 

As I initially mentioned, what you're trying to achieve is only partially possible and the reasons are already indicated in my previous post.