Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
appsac1
Frequent Visitor

Lead conversion rate with filters from other columns

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])
1 REPLY 1
grazitti_sapna
Resolver I
Resolver I

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors