Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 Number | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Grand Total |
Part A | 2 | 4 | 6 | |||||
Part B | 2 | 3 | 5 | 9 | 6 | 25 | ||
Part C | 3 | 5 | 8 | |||||
Part D | 4 | 9 | 6 | 19 | ||||
Part E | 1 | 1 | 5 | 3 | 8 | 6 | 24 | |
Total | 3 | 6 | 7 | 10 | 12 | 26 | 18 | 82 |
Solved! Go to 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
hi, @Sharksguts
Is this really a matrix visual like below:
You can also turn on or turn off the total in Format ->Subtotals ->row subtotals / column subtotals for matrix visual
Best Regards,
Lin
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?
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?
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
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.
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?
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 ,
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 .
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.
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?
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:
Without any special formatting, the matrix will provide what you are looking for.
Regards,
Tom
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |