Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Calculating 'average of non-selected' with dynamic number of filters

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

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.