cancel
Showing results for
Did you mean:
Highlighted
Mann Member

Returning desired result from different filter selection

Hi Guys,

I have a simple dataset as shown:

 100 A 200 A 250 B 270 C 340 D 400 D 430 B 500 C

My requirement is to show a measure on card for sum of sales for Country "C" and "D" only. The issue I am facing is with filter selection and getting desired results. I want following:

1) If only C is selected in filter then sum of sales of C should come like: 2) If only A or B is selected then it should show "NA" for which I created this formula:

Total Sales =
VAR TotalSales= SUM(Test[Sales])
VAR MySelection = SELECTEDVALUE(Test[Country])
Return
SWITCH(TRUE(),
MySelection = "A", "NA",
MySelection = "B", "NA",
MySelection = "C", TotalSales,
MySelection = "D", TotalSales,
TotalSales)

This is how its coming if only "A" is selected: This is what I want now, If multiple values are selected then following results should come:

a) If A and B is selected, Card should show "NA"

b) If A and C is selected (Same when B and C is selected), Card should show total sales of C only.

c) If A and D is selected (Same when B and D is selected), Card should show total sales of D only.

d) If C and D is selected, Card should show total sales of C and D.

That is I want to ignore the contributing sales of "A" and "B" when they are selected in multiple selections.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: Returning desired result from different filter selection

Try following MEASURE

Measure =
VAR check =
INTERSECT ( VALUES ( Test[Country] ), { "C", "D" } )
RETURN
IF ( NOT ( ISEMPTY ( check ) ), CALCULATE ( SUM ( Test[Sales] ), check ), "NA" )
2 REPLIES 2 Super User

Re: Returning desired result from different filter selection

Try following MEASURE

Measure =
VAR check =
INTERSECT ( VALUES ( Test[Country] ), { "C", "D" } )
RETURN
IF ( NOT ( ISEMPTY ( check ) ), CALCULATE ( SUM ( Test[Sales] ), check ), "NA" )
Mann Member

Re: Returning desired result from different filter selection

Thanks Alot! This measure works as expected!

Announcements Top Kudoed Authors
Users Online
Currently online: 407 members 4,403 guests
Recent signins:
• cindirp • atin • kpl999 • msgeneive • jhacharya • jjkmd • reddy_powerbi • rjenn • Jen_Hynds • Hnaz • jxp73 • LMidgette • filipevalentin • mottosson 