Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Measure and Visual filtering

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_PriceWon
3000Yes
4000No
2500No
1500Yes
1000Yes

 

 

 

 

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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]))

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes, thank you! This is perfect