cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fxerkan Frequent Visitor
Frequent Visitor

show grand totals top/bottom of matrix

can you please add a feature to Matrix/Table to show Grand Totals at Top/Bottom of the Matrix.

 

For example Sub Total's have this option "Row subtotal position" why not Grand Total's doesnt have this.

 

pbi Grand Total Position.png

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: show grand totals top/bottom of matrix

You can try creating a Dummy column (Total = "Total" ). This will act as a parent level hierarchy for any categorical column present in same table.

Now, create a matrix with this dummy column and your actual categorical column in "Rows" and measue in "values"

  Capture.PNG

 

Set the metric to lowest level of data by clicking icon as show below:-

 

Capture.PNG

 

Go to "Format" for Matrix visual -> Go to "Subtotals" section

Set

Row Subtotals = On

Row Subtotal Position = Top

Per Row Level = On

More options would appear

Set Total = Offand rest all = On

 

Got to  "Format" for Matrix visual -> Go to "Row Headers" section

Set 

Stepped Layout indentaion = 0 

 

and you table should look like below:-

Capture.PNG

 

Smiley Happy

7 REPLIES 7
v-xjiin-msft Super Contributor
Super Contributor

Re: show grand totals top/bottom of matrix

Hi @fxerkan,

 

As I know currently it is not supported to change Grand Total position.

 

And you can vote on this Idea.

 

Thanks,
Xi Jin.

Anonymous
Not applicable

Re: show grand totals top/bottom of matrix

One thing you can do is create a Dummy column in underlying table, something like below:-

 

DUMMY = "    Total"

 

Now this column acts as a parent hierarchy for any other categorical column in same table.

In matrix visual, take DUMMY column and your categorical column (in this case Transaction Type) and value column (In this case earnings)

 

 Capture.PNG

Select "Lowest level of data" by clicking drill down button (with hierarchy) at top left corner of matrix 

 

Now, go to Format for matrix visual:-

Go to Subtotals:-

Set Row Subtotals = On

Row Subtotal position = Top

Per Row Level = On

It will enable below options:-

DUMMY = Off

Categorical column Name <(Transaction Type in this case)> = On

and Finally :-

Go to Row Headers section:-

Stepped layout Indentation = 0 

 

You can change the name of DUMMY column to "Total" and it would give you the desired result.

Anonymous
Not applicable

Re: show grand totals top/bottom of matrix

You can try creating a Dummy column (Total = "Total" ). This will act as a parent level hierarchy for any categorical column present in same table.

Now, create a matrix with this dummy column and your actual categorical column in "Rows" and measue in "values"

  Capture.PNG

 

Set the metric to lowest level of data by clicking icon as show below:-

 

Capture.PNG

 

Go to "Format" for Matrix visual -> Go to "Subtotals" section

Set

Row Subtotals = On

Row Subtotal Position = Top

Per Row Level = On

More options would appear

Set Total = Offand rest all = On

 

Got to  "Format" for Matrix visual -> Go to "Row Headers" section

Set 

Stepped Layout indentaion = 0 

 

and you table should look like below:-

Capture.PNG

 

Smiley Happy

nivas258 Regular Visitor
Regular Visitor

Re: show grand totals top/bottom of matrix

Hi yashmanu0123,

 

Thanks for the post it really helped me. Also any chance we can format the "Total" to bold as I couldnt see option just to format the "Total" text to Bold. As the totals values are in Bold so I want to display the "Total" text also in Bold.

Any suggestions will be helpful on this.

nivas258 Regular Visitor
Regular Visitor

Re: show grand totals top/bottom of matrix

Hi yashmanu0123,

 

Thanks for the post it really helped me. Also any chance we can format the "Total" to bold as I couldnt see option just to format the "Total" text to Bold. As the totals values are in Bold so I want to display the "Total" text also in Bold.

Any suggestions will be helpful on this.

bfra4 Member
Member

Re: show grand totals top/bottom of matrix

Hi @Anonymous, 

Thanks for the resolution, this is what I needed. But is it possible to change the column's position to be the first column?

Once again, thanks and kind regards. 

Anonymous
Not applicable

Re: show grand totals top/bottom of matrix

Maybe try turning off "Stepped layout" option under "Row Headers" section under format. This should segregate the hierarchies in columns.