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).
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.
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.
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.
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...
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!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.