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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

🙂

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

There is currently no way to turn your totals to bold. Another limitation of PowerBI..

You can however change the font of your totals to Seoge (Bold). 

Rsyed203_0-1596298051252.png

Yet another workaround in PowerBI. Smh...
Bold, Italics, Underlined should be a no brainer feature they should have added, but hope this helps. 

 

Anonymous
Not applicable

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

 

🙂

@anonymous  Thank you for the brilliant solution.
May be due to recent releases of the POWER BI, we also need to do the following step in order to get the result exactly shown in the image posted where +/- sign is not there alongside the parent hierarchy.

 

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

Set Stepped Layout indentaion = 0 & also set +/- icons to Off

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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.

v-xjiin-msft
Solution Sage
Solution Sage

Hi @Anonymous,

 

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

 

And you can vote on this Idea.

 

Thanks,
Xi Jin.

The solution I have found is to ensure that "+/-" icons are turned OFF and that Stepped Layout is turned ON. This should move the totals to the bottom.

 

skcary2_0-1671475780594.png

 

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.