cancel
Showing results for
Did you mean:
Highlighted
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" )
```
Member

## Re: Returning desired result from different filter selection

Thanks Alot! This measure works as expected!

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 407 members 4,403 guests
Recent signins: