Exclude 0 values from a subtotal in a Matrix

[imranamikhan]

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?

[jdbuchanan71]

What if you add into the filter

AND 

is not

BLANK()

?

[imranamikhan]

Thanks for the quick response. No change unfortunately.

[jdbuchanan71]

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

[imranamikhan]

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

[jdbuchanan71]

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

[imranamikhan]

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

[jdbuchanan71 · Accepted solution]

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.

[imranamikhan]

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