cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KimStenager
New Member

Remove totals in matrix table

 

How do I remove totals from the rows. When I make a matrix the columns delivers a total for every column, distubing the picture.

Matrix problem.jpg

1 ACCEPTED SOLUTION
Tsanka
Continued Contributor
Continued Contributor

According to me marix properties->General->Row Totals on/off should work

View solution in original post

53 REPLIES 53
abrownfl
New Member

Format the visual.  Under Subtotals turn off Row subtotals and Column subtotals

Cornwell007
New Member

Thanks for the input. 

This was really helpful tip.

ydaoud
Frequent Visitor

In power bi 2018

Matrix ==> Click on Format ==> subtotals ==> Row subtotals ON/OFF and/OR Column subtotals ON/OFF

 

Table ==> Format ==> Totals ==> ON/OFF

You are correct that you can remove totals for tables.  However you cannot remove a grand total for a matrix at this time.  

I'm really quite surprised that we're not able to turn off grand totals on the matrix visual considering people have been requsting this enhancement for many years.  As a work-around I set the font and background color to white - not perfect, but it's OK for the time being.

 

@brycedwhite

Another workaround:
If your columns in your matrix consists of measures, then you can do

value = if(hasonefilter([highest level row column]);[valuecolumn]))

this should make the grand total row blank. and no need to change the colors.

swmcbri
New Member

Updated action list:

Select Matrix > Format tab > Subtotals > Row Subtotals > toggle to off

 

More words (same actions)

Select the Matrix >

In the visulaizations menu select "Format" tab (the painbrush) > 

Select the submenu "Subtotals" >

In the list item "Row Subtotals" Toggle the slider to off.

@swmcbri Thanks for the suggestion but we previously tried this.  The action you are suggesting removes all subtotals, including that of Revenue, Total COS and Total SG&A so it is not a suitable solution.  The solution we are looking for would be more akin to what you see in an Excel pivot where you can remove the grand totals for either rows or columns.  While Power BI does have formating for grand totals, the abilty to exclude the grand total is not an option.  Furthermore, if you were to attempt to change the font white (or whyatever your background is) the row label of "total" remains intact.  Finally, text cannot be reduced below a point size of 8 so setting it to zero is not a viable option either.  Perhaps someday Microsoft will allow the ability to exclude the grand total independent of the sub total.  

dmb33875
Helper II
Helper II

Rather than start a new thread I thought I would take my chances here on this thread.  I am looking to remove the unnecessary/unneeded total row for ths matrix.  For the finance people the numbers are coming across as accounting ready with debits left and credits right.  If I was to pull in the info mormally then the total actual revenue would have come across as -805.  Instead I use a hasonevalue measure to flip the sales vs. costs.  In doing that there is no total calculation.  I added a second table visual at the bottom to get an operating income total.  It works, but if someone has a better suggestion then I would certainly listen.  If not then just a suggestion to remove that total at the bottom of the matrix that sticks out like a sore thumb would be great.  FYI, I am using pink/blue conditional formating from the color blind friendly color scheme vs. a more traditional red/green conditional formating.  

 

Capture.JPG

ash438
Regular Visitor

how can I hide subtotals in Matrix but keeping the grand total?

RitaPT
Frequent Visitor

@KarlinOz I have the same problem that you. I only want to show some column subtotals. Did you find a solution for that?

Thanks a lot.

@RitaPT see my 2nd post here http://community.powerbi.com/t5/Desktop/URLs-in-Matrix-are-clickable-links-when-as-Rows-but-not-as/t... to see my work-around to remove specific columns from showing a total.

good input; to summarize your post in other thread:

 

a work-around for suppressing Totals of those columns which you don't want: In the formatting area > Column formatting 

Select your text column, change the Font color to match your background colour, turn Color values Off, Color subtotals and Color totals On. Repeat for all your text columns.

 

 

www.CahabaData.com

@RitaPT sadly there seems to be no solution for this. I don't know if anyone has put in a feature request for it.

please see my post on page 3 (4/20/17); it explains the design.  To reduce your totals - reduce your rows by modeling/merging row data.  for now that is the only method to manage this.  perhaps in the future there will be an explicit on/off for individual row totals but that is not there now.

www.CahabaData.com

My apologies @CahabaData, I misread your post. My problem is different to yours and to this thread - I wan't to limit totals on individual columns of a matrix not rows. I should start a new thread on that.

CahabaData
Memorable Member
Memorable Member

So I am going to add to this string because there is some confusion.  Yes the solution identified turns of ALL TOTALS.  But as per the original post - some of us want less sub-totals but still totals.  This question is not answered.

 

There is a post saying that one can right click and select Do Not Summarize - - but that post is incorrect as several have noted - it applies only to the Table visual, not the Matrix visual.

 

What is happening is that there is a Total line for each Row.  The Row fields are defined & showing in the Visual Design Pane at right.  To have a less number of Total lines you need to have a less number of Row fields.

 

In my case I had separate fields for customer name, customer number - - so I created a new calculated field that merged those 2 fields - -so then in the matrix I could have one less Row field - thus one less Total line....

 

hope this helps someone......

www.CahabaData.com

Thanks for your post CahabaData. It helped me.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.