cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
uthall
Helper II
Helper II

Grand Total Only

Hello,

 

I would like a matrix to show the grand total only, and not the subtotals..

 

There doesnt seem to be any option for this very basic requirement.

 

Am i missing something?

1 ACCEPTED SOLUTION

Yeah, Agree with @KHorseman, @uthall you should let matrix show details and turn off total row,column. and then show Grand total in Card next to matrix.

View solution in original post

16 REPLIES 16
androo
Advocate I
Advocate I

Doesn't work for me quite as Belinda describes. Not all options described from around 3:50 in the video are available to me. This is surprising as I think I have a more recent version of PBi than the one shown (I have the paintbrush formatting icon as opposed to the roller one). I have Stepped Layout and Row sub-totals on with the "Apply settings to" and "Per Row Level" also on. The choices to turn sub-totals on for specific categories are there, though the presentation is more compact and less friendly. But, the sub-total labels aren't editable, everything is just "Total" you can't name them individually as Belinda does. Nevertheless, yes, it is possible to keep an overall "Grand Total" and turn other subtitles off which is what was bothering me when I first posted here. It's still a little odd that what you can format as the "Grand Total" is also just the top level of the sub-totals. Although I can see that makes sense as that is what a Grand Total is, why not have the top-level of sub-totals labelled, in brackets, as the Grand Total. Also why isn't the Grand Total "on"/"off" setting in the "Grand Total" section, it could overide anything in the "sub-totals" section so that the grand total is "on" (i.e. it would turn on sub-totalling for the top row level of the visualisation).

KarlJ
New Member

This will work for all. 

 

https://youtu.be/UyJieY69_sE

 

* STEP1 : Row headers > Options > Stepped Layout (Turn this on!)

* STEP2 : Row subtotals > click 'ON' > Apply setting to 'Per row level' then select the first row

* STEP3 :  Row headers > Options > Stepped Layout (Turn this off!)

 

It doesn't work on my report. No. 1, the layout of Row subtotals are different now, I can only select one row level, No. 2, it shows me two subtotals, one is the grand total on top, the other is subtotal by project ID. I have not chosen project ID in my Subtotal by Project ID, and project ID is my lowest reporting level. I'm not sure why it's showing subtotal on project ID here. 

 

Is this a bug?subtotal.jpg

 

dora_kolb
New Member

You can change the subtotal text colour to white.

androo
Advocate I
Advocate I

I think this is so basic I must be missing something. But, this is trivial in an Excel standard or Power Pivot pivot, why is it so difficult or at least problematic in Power BI? Having this problem today, with row sub-totals on (which repeats every figure in my table) I have a grand total, but, turn row subtotals off, so that figures only display once and the grand total is turned off too. WTF. You can convert to a table but then you lose the grouing and expand collapse features of the PIVOT (sorry, Matrix). In general getting quick results is much faster inside Excel with power pivot, but, it seems Power BI is getting all the attention now.

shailp
Frequent Visitor

Go to Formating>Subtotal> Turn "Per row level" ON. 

 

Now you can select what granularity you want the total on. In your case I would pick date. 

Hope this helps. 

kiltannen
Advocate I
Advocate I

I realise I am a bit late to the party on this discussion, but I recently needed to do something very like this, and found a different way to accomplish it.  

 

My need was also to have multiple columns display the total - with several different measures etc. this meant I really needed to find a way for the matrix to provide this.

 

My approach was to make a 2nd copy of the matrix, then turn subtotals on, but to narrow the height of the matrix until ONLY the header and the Grand Totals row were displaying. This then meant I could place things where I needed to, display the grand totals, AND NOT the subtotals. 

 

I did place the first matrix on top (in front) of the header row on my second matrix - which allowed a bit of ugly text leakage.  This I fixed by placing a white box strategically in front of my 2nd matrix to hide the header row - but behind the 1st matrix.

Anonymous
Not applicable

I did this too, but it's a really frustrating fix for someting that BI should do with a setting. I hope they fix this eventually.

tringuyenminh92
Memorable Member
Memorable Member

Could you post the sample data and your expectation picture? so I could quickly help you to solve it.

Matrix.JPG

 

Hi, and thanks

 

See above screenshot.....

 

 

 

tjd
Impactful Individual
Impactful Individual

I have been working on the same type of problem and it seems to me that you're only going to get the solution you are looking for (right now) by either using only one category.  Seems to me that if you use more than one category in a matrix, you can only get each category's row and sub-row totals which is what you don't want.  I had to switch my visual to a table (from a matrix) in order to get the view you show; but then you lose the nice category formating of the matrix (:.

KHorseman
Community Champion
Community Champion

You can't selectively turn off subtotals in the matrix visual. You either get subtotals for everything, or no totals at all. Those are your only options. For some reason they left this basic feature out. The best workaround I've found is to write a measure that produces some of the items you would use for the non-subtotaling columns, and use that in the values.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yeah, Agree with @KHorseman, @uthall you should let matrix show details and turn off total row,column. and then show Grand total in Card next to matrix.

how to show detail I cannot fidn this option:( I also woudl liek to se teh grand total of a row but not see sub totals

Hi 🙂 how to show details as I cannot see this option my goal is to have grand total but not row totals

Ok, not a bad workaround, but hard to position the card if the number of rows in the matrix is variable depending on filters.....

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.