cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jalucchi
Frequent Visitor

Measure and filtering problem

Hi,

 

I have created a measure to calculate the discount between two values, but when I put it in a table, it shows all the record (it seems to ignore the filters), but it shows blanks in the columns that should contain the two values.

 

ex.:

I created the measure Discount = 1 - DIVIDE(SUM([Net Price]);SUM([Gross Price]))

 

Table 

Table1.PNG

 

With filter: Year = 2018, this is the result (in a table):

Table2.PNG

 

Where did I go wrong?

 

1 ACCEPTED SOLUTION

Accepted Solutions
tarunsingla Solution Sage
Solution Sage

Re: Measure and filtering problem

Hi, A minor update to @paranoodle's solution.

 

Discount = IF(ISBLANK(SUM([Gross Price])), BLANK(), 1 - DIVIDE(SUM([Net Price]),SUM([Gross Price])))

 

See if that helps.

View solution in original post

3 REPLIES 3
paranoodle Helper I
Helper I

Re: Measure and filtering problem

I think what's most likely happening with your measure is that after filtering, the measure still has a value of "1 - divide(blank, blank)", where the division result is blank, and "1 - blank" is just 1. Hence, every line that should be filtered out is going to have a value of 1.

 

Depending on the behavior you'd prefer to see and the way your data is structured, there's different ways to go about solving this, but one option would be something like this:

Discount = IF(ISBLANK([Gross Price]), BLANK(), 1 - DIVIDE(SUM([Net Price]),SUM([Gross Price])))

Which would keep your measure calculation essentially the same, but prevent it from appearing in case [Gross Price] is ever blank (such as when it's being filtered out)

 

I hope that solves your problem!

jalucchi
Frequent Visitor

Re: Measure and filtering problem

I tried to put a ISBLANK([gross price]) control, but it gave me an error due to the fact that a measure can't refer to a single value or something like that. I did read something about converting it from measure to column, but it would lose the function I was looking for. 

 

I want to add a detail: in the report I'm making, I already filtered out some fields (customer and type of item sold), and the only thing that seems to not filter the measure is the "year", which is filtered from a Date table connected to the main table. 

tarunsingla Solution Sage
Solution Sage

Re: Measure and filtering problem

Hi, A minor update to @paranoodle's solution.

 

Discount = IF(ISBLANK(SUM([Gross Price])), BLANK(), 1 - DIVIDE(SUM([Net Price]),SUM([Gross Price])))

 

See if that helps.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors