cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Matrix - hiding row totals for specific column, but keeping values for column total?

Hi all,

Not sure if this is possible, but assuming I have this visual

 HQ Field Total Month Staff Hours Staff Hours Staff Hours 1 4 160 1 40 5 200 2 4 160 0 0 4 160 3 5 220 2 80 7 300 4 4 160 1 40 5 200 Total 19 700 4 160 21 860

Is there anyway to get the visual to display the following - essentially hiding the row totals but still counting them in the column? Ideally I'd want a solution that didn't rely on setting the maximum hours as the total and hiding the lower values, as there's also times when I may want to use an average value at the bottom.

 HQ Field Total Month Staff Hours Staff Hours Staff Hours 1 4 160 1 40 2 4 160 0 0 3 5 220 2 80 4 4 160 1 40 Total 19 700 4 160 21 860

Any suggestions welcome!

1 ACCEPTED SOLUTION
Super User

Hi @twofingertyper
You can manipulate subtotals and totals with dax "isinscope" function.
For example to get the matrix as in the picture :

I used measures with the logic :

Sales with blank subtotals =

if(ISINSCOPE('Customers'[Country])|| NOT ISINSCOPE('Calendar'[Year]),[Total sales (K/\$)],"")

Profits with blank subtotals =
if(ISINSCOPE('Customers'[Country])|| NOT ISINSCOPE('Calendar'[Year]),[total profits (K/\$)],"")

The pbix with the example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

2 REPLIES 2
Helper I

@Ritaf1983 - this was perfect - thank you!

Super User

Hi @twofingertyper
You can manipulate subtotals and totals with dax "isinscope" function.
For example to get the matrix as in the picture :

I used measures with the logic :

Sales with blank subtotals =

if(ISINSCOPE('Customers'[Country])|| NOT ISINSCOPE('Calendar'[Year]),[Total sales (K/\$)],"")

Profits with blank subtotals =
if(ISINSCOPE('Customers'[Country])|| NOT ISINSCOPE('Calendar'[Year]),[total profits (K/\$)],"")

The pbix with the example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.