Lead conversion rate with filters from other columns

[appsac1]

I have information about my leads such as their lead source, lead stage, expected deal value, lead owner, region and others. One of the columns I have would be lead type where I have three types: SQL, MQL and DISQUALIFY.

I want to calculate the conversion rate of the SQL and be able to filter it by the rest of the columns. my solution was the following. I created a custom column where I put a 1 to each row that contained an SQL and 0 to the rest. I created a measure that counted each of the rows and then another measure that divided the first custom column by the measure. every time I filter by the other columns I can see that it gives me the correct data in terms of the count of SQLs and the count of all leads in terms of the filter applied.

The problem is the measure that calculates the conversion rate since it calculates the amount of SQL for the filtered field but divides it by the total number of rows in the table and not by the total number of rows by the filtered field.

This is the formula:

Conversion Rate = 'Lead Tracker'[Lead type SQL]/sum('Lead Tracker'[Count rows])

[grazitti_sapna]

Hi @appsac1 

You can try using CALCULATE function along with FILTER function. Here's an example of how you can modify your formula to achieve the desired result:

SQL Flag = IF('Lead Tracker'[Lead type] = "SQL", 1, 0)

Create a measure to count the SQL leads based on the filtered context:

SQL Count = CALCULATE(SUM('Lead Tracker'[SQL Flag]))

Create another measure to count all leads based on the filtered context:

Total Leads Count = CALCULATE(SUM('Lead Tracker'[Count rows]))

Finally, create a measure to calculate the conversion rate:

Conversion Rate = DIVIDE([SQL Count], [Total Leads Count])

Thank you 

Hope this will help you.