Forum Discussion
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:
- 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")) - 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")) - 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?
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
What if you add into the filter
AND
is not
BLANK()
?
Thanks for the quick response. No change unfortunately.
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
