remove total from sub column in matrix

[nidhs909]

Hello, 

I have a matrix with multiple level of columns as below and i would like to remove 2 columns which is marked in red. 

Your help will be appreciated. 

Thank you in advance. 

[danextian · Accepted solution]

Hi @nidhs909 

It is not possible to selectively remove subtotal  of measures in the matrix viz out of the box - it's either you disable them or show them all - out of the box that is. You can somehow achieve this using  a disconnected table containing the possible column combinations.

In the screenshot below, I would like to return the total for Revenue but not for transactions so I added an extra row for transactions and another columns to identiy the measure and the formatting.

I then proceeded with creating two new measures:

Measure_Rev_Transactions = 
SWITCH (
    SELECTEDVALUE ( DisconnectedGeo[Measure] ),
    "Revenue", [Total Revenue],
    "Transactions", [Total Transactions]
)

Revenue/Transactions Geo = 
VAR SelectedGeo =
    SELECTEDVALUE ( DisconnectedGeo[Geo] )
VAR _SelectedMeasure =
    SELECTEDVALUE ( DisconnectedGeo[Measure] )
RETURN
    IF (
        SelectedGeo = "Total",
        [Measure_Rev_Transactions],
        CALCULATE (
            [Measure_Rev_Transactions],
            KEEPFILTERS ( TREATAS ( VALUES ( DisconnectedGeo[Geo] ), Geo[Geo] ) )
        )
    )

The result is on the second matrix. Note: Total is not in bold letters because it is not a total column but a value that is present in the DisconnectedGeo table and there is no option conditionally format as column/row categories or the values to be bold.

This is just a sample to serve as a guide and the actual solution may be different.