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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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

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

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.