Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I'm trying to put together a sales dashboard. I'd like to have charts that show the performance of any selected attribute (it could be a specific country, a specific month, a specific brand...) versus the average of all other attributes in that column. The only filter that needs to be preserved is the year and any page/visual filters (which filter out the dodgy stuff!)
So, if I select Brand X, it would tell me that Brand X grew 10% versus an average of 5% for all other brands.
I'm really struggling to get a measure for sum of sales of all non-selected while keeping the year context filter and the page filters.
I've tried a million versions, but run into errors each time.
NIS Others = CALCULATE(SUM('Sales Dynamic'[NIS]), ALLSELECTED('Sales Dynamic'), ALLEXCEPT('Sales Dynamic','Sales Dynamic'[FY]))-CALCULATE(SUM('Sales Dynamic'[NIS]))
does not respect FY.
NIS Others = CALCULATE(SUM('Sales Dynamic'[NIS]), ALLEXCEPT('Sales Dynamic','Sales Dynamic'[FY]))-CALCULATE(SUM('Sales Dynamic'[NIS]))
does not respect external filters.
Ideally, what would be beautiful, would be to be able to do a calculate (sum (nis), *selected column* [no idea what the expression would be] <> *selected value*) but I have no idea how to go about this. Basically, make the selected column (all those columns affected by internal filters) a varible in itself and use it within a calculate.
Thanks,
Eva
Solved! Go to Solution.
HI, @Anonymous
You may try these formula:
NIS Others = CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ), ALLSELECTED ( 'Sales Dynamic'[FY] ) ) - CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ) )
NIS Others = CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ), FILTER ( ALLSELECTED ( 'Sales Dynamic' ), 'Sales Dynamic'[FY] = MAX ( 'Sales Dynamic'[FY] ) ) ) - CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ) )
NIS Others = CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ), FILTER ( ALLSELECTED ( 'Sales Dynamic' [FY]), 'Sales Dynamic'[FY] = MAX ( 'Sales Dynamic'[FY] ) ) ) - CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ) )
If not your case, just please share a simple sample pbix file and your expected output.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
HI, @Anonymous
You may try these formula:
NIS Others = CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ), ALLSELECTED ( 'Sales Dynamic'[FY] ) ) - CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ) )
NIS Others = CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ), FILTER ( ALLSELECTED ( 'Sales Dynamic' ), 'Sales Dynamic'[FY] = MAX ( 'Sales Dynamic'[FY] ) ) ) - CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ) )
NIS Others = CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ), FILTER ( ALLSELECTED ( 'Sales Dynamic' [FY]), 'Sales Dynamic'[FY] = MAX ( 'Sales Dynamic'[FY] ) ) ) - CALCULATE ( SUM ( 'Sales Dynamic'[NIS] ) )
If not your case, just please share a simple sample pbix file and your expected output.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin