Forum Discussion

MissBI_21's avatar
MissBI_21
Helper II
4 years ago

Conversion Rate Formula

Hi,

 

I'm trying to calculate the conversion rate of won deals out of deals won and lost by product name and date

 

The formula that I am using is:

Conversion Rate =
CALCULATE(COUNTROWS('Pipedrive DB'),
'Pipedrive DB'[status] = "Won")
/ CALCULATE(COUNTROWS('Pipedrive DB'),
'Pipedrive DB'[status] = "Won"|| 'Pipedrive DB'[status] = "Lost")

However, when I put this into a matrix, I just see the following:
 

IS someone able to tell me the correct formula to use?

Need Help

4 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    4 years ago
     
    Try this and let me know if you are able to get the result!
     
    Conversion Rate =
    Divide(CALCULATE(COUNTROWS('Pipedrive DB'),
    'Pipedrive DB'[status] = "Won")
    , CALCULATE(COUNTROWS('Pipedrive DB'), Filter(all('Pipedrive DB'),
    'Pipedrive DB'[status] = "Won"|| 'Pipedrive DB'[status] = "Lost"))
     
    another thing if you have only won or lost then the last line can be simple Countrows(Table name)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi MissBI_21 ，

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Update the formula of your measure [Conversion Rate] as below

     

    Conversion Rate = 
VAR _won =
    CALCULATE ( COUNTROWS ( 'Pipedrive DB' ), 'Pipedrive DB'[status] = "Won" )
VAR _all =
    CALCULATE (
        COUNTROWS ( 'Pipedrive DB' ),
        'Pipedrive DB'[status] IN { "Won", "Lost" },
        ALLSELECTED ( 'Pipedrive DB'[CR])
    )
RETURN 
    DIVIDE ( _won, _all, 0 )

     

    2. Set the Format of measure [Conversion Rate] as Percentage

    If the above one can't help you get the desired result, please provide some raw data(exclude sensitive data) and your expected result with correct calculation logic and special examples. Thank you.

    For example: you have the data in the below table

    What the correct conversion rate for Product pro01 is? Is it equal to 2/3=66.7% or something else?

    Best Regards

    • MissBI_21's avatar
      MissBI_21
      Helper II
      4 years ago

      Hi,

       

      Thank you for your response. This isn't quite what I'm looking for and that's because the tables in my BI file are different. I have 3 tables, deals, products and deal proctucts.

      Deal table:
      Deal ID

      Status

      Won date

       

      Products table: 
      Product ID

      Product Name

       

      Deal Products table:

      Deal ID

      Product ID

      Sum converted (total)

      Currently I have the deal products table connected to deals table (deal ID) and products table (product ID).

       

      Unfortunately I cannot seem to upload any files.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi MissBI_21 ,

        Could you please create a simplified pbix file with fake data (exclude sensitive data) and share it with me later? The file need to replicate the model(include products, deals and deal product tables) in your fact file... You can refer the following thread to upload your pbix file in your thread. And please provide your expected result with backend logic and special examples. Thank you.

        How to upload PBI in Community

        Best Regards