Filtering on measure that excludes values

[Natalie123455]

Hello,

my Booking Rate is calculated as follows: Bookings divided by Sales, where Sales from USA or Canada should not be taken into account.

I translated it into DAX as follows:

Booking Rate = DIVIDE([Bookings],([Sales without USA Canada]))

Sales without USA Canada = CALCULATE([Sales], NOT(dimoutlet[CountryName] = "USA" || dimoutlet[CountryName] = "Canada"))

This works correctly:

However, when I filter on a certain country, the booking rate is not correct. The total (last row = 33.9%) is correct but the booking rates per country are not correct.

What I would expect (TARGET STATE)

Bookings per country are divided by Sales per country.

If Country = USA or Country=Canada, then Booking Rate = 0.

What it currently does (CURRENT STATE)

Bookings per country are divided by all Sales without USA and Canada.

I understand why it calculates it that way - however, I am not sure how to guide DAX into the correct/target direction.

Any suggestions would be very much appreciated.

Many thanks!

Natalie

[lbendlin]

Also read about the IN function, and about KEEPFILTERS.

[Wilson_]

Hey Natalie,

Does it work for you if you change the Sales without USA Canada measure to the below? Filtering out Canada and USA by using FILTER will respect the filter context of the visual when it calculates the Booking Rate for you. 

Sales without USA Canada = 
VAR NonNACountries =
FILTER (
    VALUES ( dimoutlet[CountryName] ),
    dimoutlet[CountryName] <> "USA" && dimoutlet[CountryName] <> "Canada"
)

RETURN
CALCULATE (
    [Sales], 
    NonNACountries
)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Natalie123455]

This seems to work great!! Could you explain me what the difference is to this measure?

Sales without USA Canada = CALCULATE([Sales], NOT(dimoutlet[CountryName] = "USA" || dimoutlet[CountryName] = "Canada"))

[Wilson_ · Accepted solution]

Natalie,

As I mentioned, filtering out Canada and USA by using FILTER will respect the filter context of the visual when it calculates the Booking Rate for you. However, when you use CALCULATE here, the calculate modifier is on the same column of the table that you're using in the visual and therefore it overwrites the filter coming from the table.

Let me know if that doesn't make sense. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Natalie123455]

Hi @lbendlin , thanks for your quick reponse! I tried it out immediately

To my existing measures ...

Booking Rate = DIVIDE([Bookings all selected],([Sales without USA Canada]))

Sales without USA Canada = CALCULATE([Sales], NOT(dimoutlet[CountryName] = "USA" || dimoutlet[CountryName] = "Canada"))

.. I added the following measure

Bookings all selected = CALCULATE([Bookings], ALLSELECTED(dimoutlet[CountryName]))

Unfortunately, it returns the same booking rate for every country.

Is it because I missed the SUM in Bookings = CALCULATE(SUM([Bookings]), ALLSELECTED(dimoutlet[CountryName])) ? The measure Bookings is already composed of several other measures so I cannot write CALCULATE(SUM(table(field)).

Or is it due to another reason? Many thanks again for your help!!

[lbendlin]

You are not showing your measure definitions, so it is hard to tell.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

Change your approach to write the measure from the outside in.  For ALL selected countries , sum up the bookings.  Then sum up the sales (filtering out US and CA).

Finally compute your ratio .  This will work both for the Totals and for the individual values.