DAX Measure and Visual filtering

[Anonymous]

Hi all,

I got stuck trying to figure out how to solve below issue, hope someone can help me out.

I have a dataset with sales data, containing a column 'Sales_Price", and a column 'Won' with values Yes/No. 

In my dashboard I show this data in two ways:

1. As a count hitrate (simple Donut Chart stating amount of Yes and No)

2. As a conversion rate calculating the $ won as a percentage of the total amount of all possible sales. This is done in a measure (see syntax below) and shown in a Card visual as a single percentage number. 

Conversion_Rate = DIVIDE(

CALCULATE(SUM('Sales'[Sales_Price]),'Sales'[Won]="Yes"), 
SUM('Sales'[Sales_Price]))

All works well; selecting a Client filter gives me the conversation rate of that specific client - so the denominator SUM('Sales'[Sales_Price]) gets adjusted to any filter selections. However, this becomes a problem when No is selected in the donut chart. Sales[Won] are then divided by Sales Lost and this doesn't make any sense.

So I guess I'm looking for a solution where the Card Visual gets disabled when 'No' is selected in the Donut Chart, or a syntax workaround to show a N/A or Blank text when 'No' is selected in the Donut Chart. But since I'm going back and forth between these two options for a while now, without any succes - I'm open for suggestions. 

Cheers, Lise 

Sales_Price	Won
3000	Yes
4000	No
2500	No
1500	Yes
1000	Yes

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Conversion_Rate =
IF (
    SELECTEDVALUE ( Sales[Won] ) = "No",
    "N/A",
    DIVIDE (
        CALCULATE ( SUM ( 'Sales'[Sales_Price] ), 'Sales'[Won] = "Yes" ),
        SUM ( 'Sales'[Sales_Price] )
    )
)

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes, thank you! This is perfect

[amitchandak]

@Anonymous , In the donut chart you should use Won as Legned. and sum(Table[price]) as measure

Or you need two measure

Won%= DIVIDE(

CALCULATE(SUM('Sales'[Sales_Price]),'Sales'[Won]="Yes"), 
SUM('Sales'[Sales_Price]))

Lost %= DIVIDE(

CALCULATE(SUM('Sales'[Sales_Price]),'Sales'[Won]<> "Yes"), 
SUM('Sales'[Sales_Price]))

If needed use filter

Won%= DIVIDE(

CALCULATE(SUM('Sales'[Sales_Price]),'filter('Sales','Sales'[Won]="Yes")), 
SUM('Sales'[Sales_Price]))