Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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.
Hi @RudyL ,
The colum subtotal lable can't be changed dynamically base on the formula...
Best Regards
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |