Matrix total/subtotal row customization

[egrospe17]

Hello BI Gurus,

I have a matrix visual in my power BI report which displays different data in each columns. On the total row on the bottom, is there a way to customize it? For example, on a column where the data is Yes/No, i don't want to total to show. For "highway speed limit" column, I want to use average. For "# of approach lanes", i would like to use SUM. Is there an easy way to accomplish this?

[v-zhangti · Accepted solution]

Hi, @egrospe17 

You can try to control total with a function.

School Buses M =
IF (
    HASONEVALUE ( 'Table'[School Buses] ),
    MAX ( 'Table'[School Buses] ),
    BLANK ()
)

Highway Lpeed Limit M =
IF (
    HASONEVALUE ( 'Table'[Highway Lpeed Limit] ),
    MAX ( 'Table'[Highway Lpeed Limit] ),
    AVERAGE ( 'Table'[Highway Lpeed Limit] )
)

# of approach lanes M =
IF (
    HASONEVALUE ( 'Table'[# of approach lanes] ),
    MAX ( 'Table'[# of approach lanes] ),
    SUM ( 'Table'[# of approach lanes] )
)

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hi @egrospe17 .
You can try conditional formatting your total row.
Learn about it on https://www.youtube.com/watch?v=7C1vpG-JQ9I.

If this post helps you, then mark it as 'Accept as Solution' so that it could help others.

Regards,
Sanket Bhagwat