cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mann Member
Member

Returning desired result from different filter selection

Hi Guys,

 

I have a simple dataset as shown:

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

 

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:

1.PNG

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:

2.PNG

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
Super User

Re: Returning desired result from different filter selection

@Mann 

 

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
Super User

Re: Returning desired result from different filter selection

@Mann 

 

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
Member

Re: Returning desired result from different filter selection

Hi @Zubair_Muhammad 

 

Thanks Alot! This measure works as expected!

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 407 members 4,403 guests
Please welcome our newest community members: