Forum Discussion
How to exclude Items from nested DAX using multiple formulas?
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) 🙂
Anonymous , 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
2 Replies
Anonymous , 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
amitchandak Thank you!