Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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).
Very simple Solution: In "Specific Column" select "apply to Subtotal:" and "apply to Total" and have the color of the font match the color of the background. Make sure background is only one color. Data is still there but not displayed in subtotals or totals as it is ostesibly all the same color.
I wish it was this easy. I use the banded option where the report lines alternate and the totals land on both colors.
Any other sugestions?
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.
It works! Thanks!
I just ran into a similar issue. I just changed the text color for the total under a specific column to match the background.
How do you do that? I only am able to check the text color for all of them and not one specific row subtotal
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
Great adwise but it can be only used if your table doesn't have multiple color rows. Because subtotals can be on different colored row.
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.
@gooranga1Your suggestion worked wonderfully. I created a new measure (see below) and added it to the list of matrix values:
Thank you!
how to write mesure with hasonevalue function ?
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...
Oh man, that is awesome. Thank you for your time to share!
this measure is perfect exactly what i need
Thank you, this solved it!
@Cmcmahan wow thank you! This solves a major headache for me. Thank you for the time you put in describing your process.
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |