Can I add a second total to matrix visual?

[DeBIe]

Hi all,

I have a matrix visual with total documents per month per shop. I have the total, but I also would like to add the % of the total at the end of the matrix. I do not want the % of the total to be displayed in every column. Any thoughts on how I can make this happen?

Thank you very much for your time.

[v-yalanwu-msft · Accepted solution]

Hi, @DeBIe ;

Or you could create another table, then create a measure.

1.create a new table.

Newtable = 
var _a=SUMMARIZE( SUMMARIZE('Table',[date],"month",FORMAT([date],"mmmm")),[month])
var _b=SUMMARIZE('Table',"month","Total")
return UNION(_a,_b)

2.create a measure.

Measure = 
var _every=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[cate]=max('Table'[cate])&&FORMAT([date],"mmmm")=MAX('newtable'[month])))
var _row=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[cate]=max('Table'[cate])))
var _total=CALCULATE( SUM('Table'[Value]),ALL('Table'))
var _col=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),FORMAT([date],"mmmm")=MAX('newtable'[month])))
return 
SWITCH(TRUE(),
HASONEVALUE('newtable'[month])&&ISINSCOPE('Table'[cate]),IF(MAX([month])="Total",FORMAT( _row/_total,"0.00%"), _every),
HASONEVALUE('newtable'[month]),IF(MAX([month])="Total","100%",_col),
ISINSCOPE('Table'[cate]),_row,_total)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

Thank you @v-yalanwu-msft , this will do for now!

[DeBIe]

Hi @v-yalanwu-msft thanks for this solution. I will try it asap and let you know if it worked. Thanks!

[v-yalanwu-msft]

Hi, @DeBIe ;

You could add another masure.

% total = SUM([Value])/CALCULATE(SUM([Value]),ALL('Table'))

Then adjust it.

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

Hey @v-yalanwu-msft ,

Thank you for your reply and suggest solution. I am wondering how this will be maintainable towards the future. I have the same dashboard live for 15 customers. It means that I need to adjust the column width of the new month every time a new month starts?

Thanks

[amitchandak]

@DeBIe , You can have only one total, or based on subtotal . You can not add additional one

Option are Hybrid table

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[DeBIe]

Hi, @amitchandak  thank you for your reply.

Do you mean adding a second table/matrix with the same info, except then showing the values as % of the total in the second table? Or do you mean I can quickly switch between % and totals (waiting for report parameters function to do this)