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

[Anonymous]

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

[v-lili6-msft · Accepted 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