Column grand totals in a matrix, but only when more than one column is in my filter

[RudyL]

Hi,

Let's take a simple matrix: years as columns, countries as rows and revenue as values. Next to the matrix is a multi select slicer for years. 

I have the column subtotals option ON in the visual so i can select a couple of years and the totals column adds up these values per country row. But when I select only one year. The matrix has a column for that year and a totals column that is identical to the one year selected. 

Guess my question: can you hide a totals column from a matrix when only one column is selected. For those who try to suggest conditional formatting values so totals become white on white, i want the totals column header to be hidden as well.

[v-yiruan-msft · Accepted solution]

Hi @RudyL ,

Base on my research, there is no built-in functionality in Power BI to conditionally hide the total column in a matrix visual based on the number of columns selected. The total column in a matrix visual will always be displayed if the “Column subtotals” option is turned on, regardless of the number of columns selected in the slicer...

Assume that you have a matrix visual below and apply the below fields onto the matrix.

Then you can create a measure as below to get the sum of values:

Adjusted Sales = 
VAR SelectedYears =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Year] ), ALLSELECTED ( 'Table'[Year] ) )
RETURN
    IF (
        SelectedYears = 1
            && NOT ( ISINSCOPE ( 'Table'[Year] ) ),
        BLANK (),
        SUM ( 'Table'[Sales] )
    )

You can find the details in the attachment.

Best Regards

[RudyL]

Nice sort-of-solution. I like the way you play DAX.

But in the end the column header 'total' is still visible. I was looking for a full solution that this would be conditional as well. But as you mentioned, functionality is not known to be provided. Thanks anyway for responding.

[v-yiruan-msft]

Hi @RudyL ,

The colum subtotal lable can't be changed dynamically base on the formula...

Best Regards