Matrix don't show subtotals of 0

[MoMrCrane]

In a Matrix, I only want to see rows where the subtotal is non-zero. If it's negative, I want to see it. If it's positive, I want to see it. If it's 0, I don't want to see it. How do I hide this row for "ARI-004- ARI - Automotive Rentals, Inc." from the matrix?

[danextian · Accepted solution]

Hi @MoMrCrane 

Try this as a separate measure and use it in the visual level filter. You will need to get the  total per customer.

ROUND (
    CALCULATE (
        SUM ( 'table'[column] ),
        ALLEXCEPT ( 'dimension table', 'dimension table'[customer] )
    ),
    2
)

[MoMrCrane · Accepted solution]

I didn't know how to grab the permalink, but @v-lionel-msft answered my question with his post on this other question. Thank you.

https://community.fabric.microsoft.com/t5/Desktop/Filter-by-Sub-Totals-in-Matrix/m-p/876141/highligh...

[danextian · Accepted solution]

Hi @MoMrCrane 

Try this as a separate measure and use it in the visual level filter. You will need to get the  total per customer.

ROUND (
    CALCULATE (
        SUM ( 'table'[column] ),
        ALLEXCEPT ( 'dimension table', 'dimension table'[customer] )
    ),
    2
)

[MoMrCrane]

Actually this is the answer, but I didn't have to round. thank you. 

[MoMrCrane]

Hi,

I tried the measure and set it to both Not equal to 0 and also greater than .000000001 or less than -.000000001 and both still show customers where the invoice total is 0. Do you have other ideas on how to make these $0 customers not show up in the matrix?

[danextian]

Must be a case of how precise your decimals are. I would add a ROUND to the measure being used

ROUND ( [original measure], 2 )

And then in the visual filter, use this updated measure to filter the visual to not equals 0

[MoMrCrane]

I'm not using a measure, i'm just using the Row Subtotals in Format Visual of the Matrix. How would I create a measure to sum the invoice totals for each customer in a Matrix?  

I created the measure 

and set it to show items when the value is not 0 but it doesn't hide the 0 invoice total rows.

[danextian]

You need to round the returned value.

ROUND( SUM ('Table'[Column), 2)

[MoMrCrane]

I rounded the measure then set it as a filter on the Matrix visual but this customer still shows up even though their subtotal is 0.

[Unknown_Var]

Set a filter to not equal or contain 0. 

May need to use between if rounding is involved than set filters to is greater than .00000001 and less than - .000000001.

*Note: Can use this trick for conditional formatting. 

Upwards and Onwards!

[MoMrCrane]

Where is the filter for subtotal, so I can set it to 0? If it were the data I can do it, but I don't know how to set a filter for a subtotal of a matrix?