Forum Discussion
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.
This looks simple but I am really struggling to do it, can anyone please help?
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! :smileyhappy:
JC
2 Replies
Hi Anonymous
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).
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! :smileyhappy:
JC
