Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
imranamikhan
Helper V
Helper V

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

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.

View solution in original post

8 REPLIES 8
jdbuchanan71
Super User
Super User

What if you add into the filter

 

AND 

is not

BLANK()

 

?

Thanks for the quick response. No change unfortunately.

 

Sample 2.PNG

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 filterNo filterVAR filterVAR filter

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:

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.