Forum Discussion

MissBI_21's avatar
MissBI_21
Icon for Helper II rankHelper 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('Pipe...
Need Help
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
    Icon for Helper II rankHelper 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