cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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] ) )```

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.
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] ) )```

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.