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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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