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 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

Need Help

v-lili6-msft
7 years ago
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

19 Replies

parry2k
Super User
7 years ago
Sharksguts you should be able to show row and column subtotal in matrix visual. You can turn this on and off in format pane.
TomMartens
Super User
7 years ago
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
v-lili6-msft
Community Support
7 years ago
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
- Sharksguts
  Frequent Visitor
  7 years ago
  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
  - v-lili6-msft
    Community Support
    7 years ago
    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