Exclude Certain Subtotals From Matrix

[Anonymous]

I am creating a table using a matrix visualization in Power BI. I have row subtotals onsince certain columns in my table make sense with subtotals but there are some column subtotals that I would like to exclude entirely. Not only do they not make sense, but it is simply repetitive. If I try to exclude one of the values (in the row hierarchy), the entire column disappears. If I try to exclude the total value of the row hierarchy, the entire section of the hierarchy disappears. 

Is there a way for me to only show certain subtotals from my matrix? Ideally, I would like to exclude subtotals (or averages) from a few columns without making subtotals disappear entirely.

The links below tell me that I am able to turn on and customize "column subtotals." However, I have just updated Power BI to the latest version and I do not see that as an option under Format > Subtotals. My only option is row subtotals. 

https://community.powerbi.com/t5/Desktop/Turn-Off-Certain-Subtotals-in-Calendar-Matrix/m-p/556577#M2...

https://www.wiseowl.co.uk/blog/s2553/matrix-subtotals.htm

[jdbuchanan71]

With your matrix selected, under the Subtotals card you can find the per row / per column settings.

You should be able to use that to accomplish what you are after unless I misunderstood.

[Anonymous]

@jdbuchanan71 Not sure why I don't have that option. I know that I have the latest version of Power BI. Here is what mine looks like:

Could it be because I am using a row hierarchy?

[Cmcmahan]

You will see per row level as an option when your data is grouped in rows and you have row subtotals turned on, and per column level as an option when your data is grouped by columns and you have column subtotals turned on.  

I've run into this same issue before.  There's no elegant way to solve it. You can read about how I managed to fix it here

Essentially, you have to abuse the fact that PowerBI doesn't actually sum measures in matrix totals, it recalculates them in a different context.  Let me know if you have further questions after reading the linked thread.

[Anonymous · Accepted solution]

Hi @Cmcmahan -- thanks for that. Honestly, my table looks a lot less cluttered without subtotals so I may just exclude them entirely. They should really have an easy feature that lets you pick and choose which subtotals you wnat to display!

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[jdbuchanan71]

If you turn on "Per row level" what do you see under it?

[Anonymous]

@jdbuchanan71 No, just the option of switching my row value subtotals on.