Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
leofdasilva29
New Member

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

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).

16 REPLIES 16
Harsha7447
New Member

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!

Skorcamp
Advocate I
Advocate I

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
Not applicable

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.

gooranga1
Power Participant
Power Participant

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:

 

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

 

Thank you!

Anonymous
Not applicable

how to write mesure with hasonevalue function ?

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

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...

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!

chotu27
Post Patron
Post Patron

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.