Creating a measure that excludes a subset that is in a lookup table

[AviBI]

Hi all

Appreciate if you could help with this

So I have dataset which comprises all stores within England and I am creating some reports for comparing items performance of a subset (stores of a certain company - lets call it ACME) with the Rest of England Stores. The Stores within the subset are in a separate lookup table. 

I have defined the following measures:

NationalItems = Sum('ItemsData'[Items]) - There is an Items Column in the Items Table 

ACMEItems = Calculate ('ItemsData'[NationalItems] , FILTER(ACMEStores,ACMEStores[Group] ="ACME")) - I have a Group Column in the lookup table (ACMEStores) that has the string value "ACME" in each row. This then allows me to use this column as a filter on the main data table

Now my question is how do I calculate a measure that will give me the Rest of England items i.e. all items for those stores that are not ACME stores. 

Quick response would be much appreciated

[harshnathani]

Hi @AviBI ,

ACMEItems = Calculate ('ItemsData'[NationalItems] , FILTER(ACMEStores, NOT ACMEStores[Group] IN {"ACME"})

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[AviBI]

Hi Harsh

Thanks for the speedy response and solution

Your solution did not work for some reason but I did manage to find a solution. I basically filtered the Data Table using "<> ACME" condition on the relevant lookup table column 

Many thanks once again for your solution 

[harshnathani]

HI @AviBI ,

Happy to helps 🙂

Regards,

Harsh Nathani

Appreciate with a Kudos