Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
EDWF. = CALCULATE(SUM('FPI & Accounts Fusion & EDWF Data'[POSTED VALUE]),'FPI & Accounts Fusion & EDWF Data'[Data Source]="EDWF"))
Fusion. = CALCULATE(SUM('FPI & Accounts Fusion & EDWF Data'[POSTED VALUE]),'FPI & Accounts Fusion & EDWF Data'[Data Source]="Fusion"))
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?
Solved! Go to 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.
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.
I have run a full refresh but unfortunately that has not resolved the issue either. May I send you my working file?
Certainly, if you post it to a dropbox or onedrive I can look at it.
Thank you. I have just shared a link via private message
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.
Thank you so much for solving what now looks so obvious. Appreciate your patience and support
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |