Remove subtotal from only one column in a Matrix in Power BI

[leofdasilva29]

Good afternoon to all (here in Brazil is a beautiful Friday afternoon).

I would like to know if it is possible to disable the subtotal of only one column in Power BI in an matrix?

In this matrix I have some numerical columns where the subtotal does its paper well, and I have a column of text (justification of values that are negative).

In this justification column, I wish I did not have the subtotal (because as it is text, it brings either the First record or the Last one).

[Harsha7447]

If you do not want row subtotal for a specific column , you can try adding that field to 'Rows' section in visualizaton pane. If 'row subtotal' is enabled in the 'Format' in visualizaton pane, then your field appears in the matrix as a row header with a 'Total' label . Then you can go to 'Subtotal' section of the 'Format' in visualizaton pane and turn on 'Per row level' . Then each row header field appears with a  turn on/off toggle for 'Total' label . Then you scroll down to get your field and turn off the toggle under it. Your field  appears without any aggregation or labels. It appears as a row header which may look different from other columns . You can do appropriate changes in the font colour and background colour of the matrix and try using 'apply to labels' option to get the required appearance. 

Note that it  may have certain limitations as your field appears as  a row header.

[wml907]

It works! Thanks!

[Skorcamp]

I just ran into a similar issue. I just changed the text color for the total under a specific column to match the background.

[Anonymous]

How do you do that? I only am able to check the text color for all of them and not one specific row subtotal

[Skorcamp]

1. Click on your table or matrix visual.

2. Click on Format section of the Visualizations Pane. 

3. Under Visual, go to 'Specific Column'

4. Under Series change the drop-down to the column name

5. Toggle 'Apply to Values' to Off and 'Apply to total' to On.

6. In the next connected section [Values], change test color to white.

7. You may need to change the style preset to none. 

Good Luck

[gooranga1]

If the values being subtotaled could be made into a measure you could use HASONEVALUE to only calculate the measure when there is one value and you could use the IF statement to set the measure to BLANK() so the subtotals will always be blank.

Not sure it's what are asking, maybe a screenshot of your data might help.

[mmarriott]

@gooranga1Your suggestion worked wonderfully.  I created a new measure (see below) and added it to the list of matrix values:

LastNameForMatrixGrid = IF(HASONEVALUE(vwMonthlyDetail[LastName]), VALUES(vwMonthlyDetail[LastName]), BLANK())

Thank you!

[Anonymous]

how to write mesure with hasonevalue function ?

[mkr4000]

(Name of Measure) = IF(HASONEVALUE(table[filed of importance]),DISTINCT(table[filed of importance]),BLANK())

[Cmcmahan]

There's a better way.  I just had this same adventure myself, as chronicled here: https://community.powerbi.com/t5/Desktop/Only-Total-Select-Values-in-a-Matrix-amp-Formatting-Help/m-...

The solution is simpler than it seems.  Create a measure for the column in question with the following code:

Measured_Text = IF(ISFILTERED('Table'[Row Grouping]),VALUES('Table'[Text field]),BLANK())

And plug that measure into the matrix instead of the raw value.  The [Row Grouping] should be whatever the lowest level in your matrix rows is.

Also, please vote on this idea, so we don't have to go through all this in order to hide totals: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...

[MC-44]

Thank you, this solved it!

[rtbergin]

@Cmcmahan wow thank you! This solves a major headache for me. Thank you for the time you put in describing your process.

[bocian74]

Man, you are my hero! 😉 I've been struggling with this and tried a few solutions. There are a few other simillar ones (e.g. based on HASONEVALUE) but it still shows subtotals if there is only one row at the lowest level. Your solution works perfectly. BIG THANKS!

[chotu27]

@leofdasilva29  No it is not possible in power bi Post idea here