Conversion Rate Formula

[MissBI_21]

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?

[Anonymous]

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]

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]

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

[VijayP]

@MissBI_21 

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)