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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sharksguts
Frequent Visitor

Matrix Column Grand Total

I have searched the forumn but can't find the answer. I am using a matrix to get the table I am after, however cannot get it to show the Grand Total Column at the end. Should I be using a Matrix and  if so how can you show the Column grand total.

 

Part NumberWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Grand Total
Part A2   4  6
Part B 235 9625
Part C 3  5  8
Part D  4  9619
Part E11 538624
Total3671012261882
1 ACCEPTED SOLUTION

hi, @Sharksguts

After my research, I'm afraid it couldn't achieve in Power BI for now.

But you may try this way, add a matrix or table visual with only column Part Number and Value, then they will be the Grand Total for

basic matrix column, and they can interact with each other.

For your requirement, you could post your new idea in Power BI ideas and make this feature coming sooner.

 

another question, for you want to show the values of every day for the column, this exceeds the maximum number of items that can be displayed for column, Not only does it not have grand total but also the sub total.

So you could these two ways:

1. move the column Values field into row and row Value field into column.

2. use a slicer to filter data to reduce the number of items of date.

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

19 REPLIES 19
v-lili6-msft
Community Support
Community Support

hi, @Sharksguts

Is this really a matrix visual like below:

1.JPG

You can also turn on or turn off the total in Format ->Subtotals ->row subtotals / column subtotals for matrix visual

 

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OK, I see my problem I haven't explained myself properly. I had created a matrix with multiple columns like below.

If you turn subtotal on it puts a total for each column and the grand total. Any way to just show the grand total?

 

 

image.png

 

The other problem I have is with my actual data, I get the following message which is why I had the sub total but no grand total.
What are my options for handling this?

 

image.png

 

Apologies for the poor explanation.

 

Regards


Richard

hi, @Sharksguts

After my research, I'm afraid it couldn't achieve in Power BI for now.

But you may try this way, add a matrix or table visual with only column Part Number and Value, then they will be the Grand Total for

basic matrix column, and they can interact with each other.

For your requirement, you could post your new idea in Power BI ideas and make this feature coming sooner.

 

another question, for you want to show the values of every day for the column, this exceeds the maximum number of items that can be displayed for column, Not only does it not have grand total but also the sub total.

So you could these two ways:

1. move the column Values field into row and row Value field into column.

2. use a slicer to filter data to reduce the number of items of date.

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

image.png

 

Untick row or column subtotals

I am trying to get it to just show the grand total if I switch off sub totals then I get no grand total column.

image.png

 

Hey I know it is very old but putting the response for others.

 

Go to Visual -> Row subtotals -> Turn on 'Per row level' option under 'Apply setting to' head, and choose the very first column of your visual, move to next head 'Rows' and turn on 'Show subtotal' option - put text for sub total label e.g. 'Total' or 'Grand Total'. Now chose next columns one by one under 'Apply settings to' option and this time turn off 'Show subtotal' option under 'Rows' head.

This is the answer I needed. Just one more thing, when I export, it's telling me that it exceeded the allowed volume and hence, not showing the Totals. When I use less records it works just fine, however I think it's weird since I'm not exceeding the 150K record's limit (Barely 15K as shown on screenshot. Is there something I'm not seeing? 

ArmandoMtz_0-1717524019098.png

 

Probably your matrix has multi tier columns or multiple columns which increases the numer of intersections due to which even when you have less than 150K rows you are not able to export it completely because it is number of intersections not rows which caps the limit.

 

In such a situation I use PowerBi Paginated Report which let you subsribe/download the report in tabular format, in case you need in matrix format you may edit the paginated report in Power Bi Report Builder then publish it back.

 

Refer below solution for more detail

 

Solved: Re: Export Data to Excel - Export Data exceeded. - Microsoft Fabric Community

Hi Mate ,

Screenshot 2024-02-05 121900.png

I followed same as u said but still for each row the total value is shown which I don't want, only grand total value required ,how to acheive that .
Thanks in Advance .

it seems row subtotal is turned on for 'Year', you need to turn that off. to do that follow the steps from my answer (earlier post).

nope If I turnoff row subtotals the grand total values are not displayed .

sailochanar_0-1707125305065.pngsailochanar_1-1707125329578.png

this way the data is displayed.

 

Turn on the Row Sub Total which appears in your screen shot, then expand this option by click at left arrow like icon, you will find row level option select 'Year' from the drop down, just below that you will see 'Show Subtotal' which will appear as 'on', turn that off and you will be able to see only grand total (which is actually a subtotal at 'Country' and the same you are leaving turned on).

Thanks ,It worked .

Great, please do mark my answer as 'accepted solution'!

Thank you! That is exactly what I was looking for.

@Sharksguts

 

I think you cannot do this...

But the workaround will be to turn subtotals on and put font and background color the same as the color of the cell (this way it till be hidden).

Then change the font color of the grand total to a color different from the one in the cell

So should I be using something else instead of a Matrix if I want to display some data in a table with grand totals?

TomMartens
Super User
Super User

Hey,

 

not sure if I'm missing something, but the screenshot shows the Table visual (to demonstrate how the data looks like) and the Matrix visual and also the assignment of the columns to the bands of the visual:

image.png

Without any special formatting, the matrix will provide what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
parry2k
Super User
Super User

@Sharksguts you should be able to show row and column subtotal in matrix visual. You can turn this on and off in format pane.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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