switch

[IF]

Hi,

I have the following data.

Month	Unit	Sold	Total
12.2020	A	10	12
12.2020	B	9	10
12.2020	C	5	7
12.2020	D	3	4
12.2020	E	15	17
12.2020	F	20	20
01.2021	A	11	12
01.2021	B	8	10
01.2021	C	5	6
01.2021	D	2	3
01.2021	E	14	18
01.2021	F	20	22

I want to exclude the units "E" and "D" when I have the overall view. I do it with:

Measure = CALCULATE(DIVIDE(SUM(Main[Sold]), SUM(Main[Total])), FILTER(Main, Main[Unit]<>"E" && Main[Unit]<>"D"))

This works. However if i select E in slicer, it shows blank value in the card. However I want to see the value (0.828). Similar thing is valid for the D as well. I think I should use SWITCH, but don't know exactly how to use it. Could you help me?

Thanks in advance.

[v-jayw-msft]

Hi @IF ,

The table will be filtered by the slicer. If you select unit "D" or "E" in the slicer, there's no value to be calculated.

I don't understand the logic of the formula you are using because it just calclualtes the divided value of slicered [solid] and [total].

And I don't know how do you get 0.828. Can you please share the logic to us?

Best Regards,

Jay

[amitchandak]

@IF , Try

Measure = CALCULATE(DIVIDE(SUM(Main[Sold]), SUM(Main[Total])), FILTER(Main, not(Main[Unit] in {"E" ,"D"})))

[IF]

I tried but it doesn't work if I select unit "D" or "E" as well.