cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Exclude 0 values from a subtotal in a Matrix

Hi everyone,

I am trying to exclude 0 values from a column subtotal in a Matrix. Using a Report Level Filters does not seem to work in this scenario.

I have three measures:

1. The first measure is summing a column based on a condition:

`EDWF. = CALCULATE(SUM('FPI & Accounts Fusion & EDWF Data'[POSTED VALUE]),'FPI & Accounts Fusion & EDWF Data'[Data Source]="EDWF"))`
2. The second measure is summing a column based on a different condition:

`Fusion. = CALCULATE(SUM('FPI & Accounts Fusion & EDWF Data'[POSTED VALUE]),'FPI & Accounts Fusion & EDWF Data'[Data Source]="Fusion"))`
3. The third measure is a simple variance calculation - it subtracts the first measure from the second:

`VAR = 'FPI & Accounts Fusion & EDWF Data'[Fusion.]-'FPI & Accounts Fusion & EDWF Data'[EDWF.])`

How can I filter the matrix to only display rows where the 3rd measure does not equal 0?

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

Re: Exclude 0 values from a subtotal in a Matrix

It's becuase the VAR amounts are smaller than .01, in the grid they show 0 because of the format but if you change the VAR format to something with say 5 decimal places you can see it:

If you change your Var. measure to the following it should work better for you:

`VAR. = ROUND([EDWF]-[Fusion],2)`

Also, you don't have to (and shouldn't) put the table name in your formula when referencing a measure.  That way, when you are looking at a formula you know  'Brand Fusion & EDWF Data'[Value] is a column in a table and [EDWF] is a measure.

8 REPLIES 8
New Contributor

Re: Exclude 0 values from a subtotal in a Matrix

What if you add into the filter

AND

is not

BLANK()

?

Frequent Visitor

Re: Exclude 0 values from a subtotal in a Matrix

Thanks for the quick response. No change unfortunately.

New Contributor

Re: Exclude 0 values from a subtotal in a Matrix

Is it maybe that the visual just isn't refreshing?  I made a sample model to test and am getting the behavior you are looking for.  First image is unfiltered, second is filtered on VAR measure only.

No filterVAR filter

Frequent Visitor

Re: Exclude 0 values from a subtotal in a Matrix

I have run a full refresh but unfortunately that has not resolved the issue either. May I send you my working file?

New Contributor

Re: Exclude 0 values from a subtotal in a Matrix

Certainly, if you post it to a dropbox or onedrive I can look at it.

Frequent Visitor

Re: Exclude 0 values from a subtotal in a Matrix

Thank you. I have just shared a link via private message

New Contributor

Re: Exclude 0 values from a subtotal in a Matrix

It's becuase the VAR amounts are smaller than .01, in the grid they show 0 because of the format but if you change the VAR format to something with say 5 decimal places you can see it:

If you change your Var. measure to the following it should work better for you:

`VAR. = ROUND([EDWF]-[Fusion],2)`

Also, you don't have to (and shouldn't) put the table name in your formula when referencing a measure.  That way, when you are looking at a formula you know  'Brand Fusion & EDWF Data'[Value] is a column in a table and [EDWF] is a measure.

Frequent Visitor

Re: Exclude 0 values from a subtotal in a Matrix

Thank you so much for solving what now looks so obvious. Appreciate your patience and support