Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
75 | |
60 | |
37 | |
33 |
User | Count |
---|---|
102 | |
56 | |
52 | |
45 | |
40 |