Column totals for matrix with specific layout

[fpennisi17]

Hi,

I have a matrix with no stepped layout, two row fields (part of a hierarchy), no column fields and multiple value fields.

So basically, except for the two row headers, all the table columns are values.

There are no subtotals.

I would like to add a footer row showing the total for some columns.

Is there a way to achieve that?

[amitchandak]

@fpennisi17 , Not very clear. Matrix does how Grand Total. Can you explain with an example or screenshot

Also refer: https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

[fpennisi17]

In this example I reproduced, in Excel, my matrix layout.

[v-alq-msft · Accepted solution]

Hi, @fpennisi17 

Based on your description, I created data to reproduce your scenario.

Table:

You may create measures as below.

Measure1 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value1]),
    BLANK()
)
Measure2 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value2]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value2])
    )
)
Measure3 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value3]),
    BLANK()
)
Measure4 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value4]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value4])
    )
)
Measure5 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value5]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value5])
    )
)

Then you need to make the matrix selected, go to 'Visualizations' ribbon, click 'Format'=>'SubTotals', set 'Row subtotals' on.

Result:

Best Regards

Allan

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

[fpennisi17]

It worked for the data.

I didn't want the intermediate subtotals, so I also unchecked them for first level of hierarchy, by enabling "per row level" option.

In this way, I had to apply the condition you suggest (if not in scope then blank) only in the measures for which I don't want the total.

By the way, one annoying thing remains. I don't know why, but albeit the totals take only the height of one row, the footer row is much higher (see screenshot)

How can I eliminate that space?

[v-alq-msft]

Hi, @fpennisi17 

I wonder if you have set some other formats. You may try 'Revert to default' and then reset them. Here is my setting in 'Subtotals'.

Result:

Best Regards

Allan

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

[fpennisi17]

I understood the issue.

In my table there is a value column which is hidden, that I use for sorting.

I forgot to apply the condition on the scope on that column, so the subtotal of that column spanned taking that space.

I just need to apply that condition and everything worked like a charm.

Thank you very much!

[HarryBailey]

Hi there - are the values in your data source in PowerBI listed as values? I've had this before where they weren't totalling, had to change the type from text to whole number within PowerQuery.

[fpennisi17]

I made a measure for all of them.

I've set the type for every measure. Some are numeric, some others are textual.