cancel
Showing results for
Did you mean:
Regular Visitor

## Allselect except one slicer

Hi,

I have a simple table which I want to create a measure.

An extract of the table is here:

I would like to create a measure, so that when I filter on product, it will return the sales of all the product that has the same color as the chosen product in 2010. E.g. If I filter on Memo, the measure will return me the sales of all  stationary that is blue in 2010, in this case will be 1883.

I figured out ALLSELECTED might do the trick, but the challenge is I have different classes of product, so I want to keep the "Class" filter here i.e. only the blue stationary but not other class of products that are blue.

I tried:

CALCULATE(
SUM(Sales[Sales]),
FILTER(ALLSELECTED(Sales),Sales[Year] = 2010)
)

But this will also filter on the chosen product i.e. gives me 332 if I select memo. Essentially I would like to keep the "Class" filter active but not the "Product" filter.

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Allselect except one slicer

Hi @AlB ,

Thank you for your answer. Unfortunately the measure doesn't work. It returns an error saying that multiple value was returned while a single value was expected. However, I do manage to create a measure that works based on your idea:

```Measure =

VAR _Color = CALCULATE(SELECTEDVALUE(Sales[Color]), Sales[Year] = 2010)

RETURN
CALCULATE(
SUM(Sales[Sales]),
ALL(Sales[Product]),
Sales[Color] = _Color,
Sales[Year] = 2010
)```

Thank you again for your help!

JC

2 REPLIES 2
Super User

## Re: Allselect except one slicer

Hi @gcheuk

Assuming all fields are on the Sales table, try this measure in a card visual, with the two slicers you show:

```Measure =
VAR _Color =
CALCULATE ( DISTINCT ( Sales[Color] ), Sales[Year] = 2010 )
RETURN
CALCULATE (
SUM ( Sales[Sales] ),
FILTER ( ALL ( Sales[Color] ), Sales[Color] = _Color ),
Sales[Year] = 2010,
ALL ( Sales[Product] )
)```

If this doesn't work please show a  sample of your tables. Always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Regular Visitor

## Re: Allselect except one slicer

Hi @AlB ,

Thank you for your answer. Unfortunately the measure doesn't work. It returns an error saying that multiple value was returned while a single value was expected. However, I do manage to create a measure that works based on your idea:

```Measure =

VAR _Color = CALCULATE(SELECTEDVALUE(Sales[Color]), Sales[Year] = 2010)

RETURN
CALCULATE(
SUM(Sales[Sales]),
ALL(Sales[Product]),
Sales[Color] = _Color,
Sales[Year] = 2010
)```

Thank you again for your help!

JC

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,356)