cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
imranamikhan Frequent Visitor
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.


Sample.PNG

 

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
jdbuchanan71 New Contributor
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:

 

VarFivePlaces.jpg

 

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
jdbuchanan71 New Contributor
New Contributor

Re: Exclude 0 values from a subtotal in a Matrix

What if you add into the filter

 

AND 

is not

BLANK()

 

?

imranamikhan Frequent Visitor
Frequent Visitor

Re: Exclude 0 values from a subtotal in a Matrix

Thanks for the quick response. No change unfortunately.

 

Sample 2.PNG

jdbuchanan71 New Contributor
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.

 

VarZeroNoFilter.jpgNo filterVarZeroFilter.jpgVAR filter

imranamikhan Frequent Visitor
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?

jdbuchanan71 New Contributor
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.

imranamikhan Frequent Visitor
Frequent Visitor

Re: Exclude 0 values from a subtotal in a Matrix

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

jdbuchanan71 New Contributor
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:

 

VarFivePlaces.jpg

 

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 Frequent Visitor
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