How to exclude Items from nested DAX using multiple formulas?

[jmars808]

Current working formula I want to exclude 4 items from:

Best in Class Top 5 Average = CALCULATE(AVERAGEX(TOPN(5, SUMMARIZE(filter(all(Company),Company[Segment]="SMB"),[Company Name],"NROI", [Item Value]),[Item Value],DESC),[Item Value]),all('Global Region'))

I need to exclude 4 different Company Names from the total list of companies in the SMB Segment. I need to exclude Company A, Company B, Company C, and Company D. Right now it is taking the top 5 average from all Company Names. I need to to take the top 5 avg from all except these 4. 

How do I write the syntax properly so this works? I assume something with Company Name <> "Company A", Company Name <> "Company B", etc. Not sure where to properly exclude these. Tried ALLEXCEPT but that didnt work. Stuck here on how to write this...

Thanks!!
(DAX Novice) 🙂 

[amitchandak · Accepted solution]

@jmars808 , Try like

Best in Class Top 5 Average = CALCULATE(AVERAGEX(TOPN(5, SUMMARIZE(filter(all(Company),Company[Segment]="SMB" && not( [Company Name] in {"Company A", "Company B", "Company C", "Company D"})),[Company Name],"NROI", [Item Value]),[Item Value],DESC),[Item Value]),all('Global Region'))

If you want to exclude on measure and not in other. create those two separately and then use in this formula

[jmars808]

@amitchandak Thank you!