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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jakewatson
Frequent Visitor

conversion rate calculation

I want to create conversion rate calculations that cross from Lead table into Deal table. Every Deal has an associated Lead. I want to create a conversion funnel from Lead Created On to Deal Close Date. Specifically I want to create conversion for Leads created in a given month who's associated Deal was closed in the same month, and then Leads that were created in a given month who's associated Deal was closed in the subsequent two months, three months and so on.

 

I've tried creating metrics and columns but can only select columns from one of the two tables. I've also tried merging the two tables with the same outcome. 

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @jakewatson ,

Is there any reason that your Lead table and the Deal table have to be in separate tables?  I would have imagined that the Lead table and your Deal table can be combined using a unique identifier key.  Is this assumption correct?  In that case, I would combine the Deal table and Lead table in one table and show the lead date and deal date side by side to enable the use of sumxing over the table for the duration of lead being converted into a deal having the calendar dimension table as a disconnected table for flexible duration analysis. I've created a dummy data to demonstrate the duration analysis that can be done.  From your descriptions I guess that your cut off dates for counting is calendar months instead of number of days which have elapsed, so for example, 5/30 and 6/1 are in the different month despite it is less than 30 days.  

To tackle a issue like this, for example, you can write a formula like below to produce the visualization as shown below which shows how many calendar month over total months to deal completion has elapsed since the lead was created for each of the given point in time.  

DataNinja777_1-1717833816948.png

Which will give you an output like below (I repeat again, which shows how many calender months out of the time to deal completion has elapsed at any given point in time.)   

 

DataNinja777_2-1717833927183.png

 

I attach an example pbix file.  The solution above may not be in line with your exact output requirement, but hopefully it will provide some idea about how to tackle a duration and time related analysis using a disconnected calendar table.    

 

Best regards,

View solution in original post

3 REPLIES 3
v-jianpeng-msft
Community Support
Community Support

Your solutions is so great @DataNinja777 . At the same time, I am also very grateful to @DataNinja777 and @TomMartens  for caring about this case

Hi, @jakewatson 

Have you solved the current problem? If so, you can share your non-privacy-inclusive solution here or mark your helpful reply as a solution so that other community members can quickly find answers to similar questions. Thank you again for your cooperation.

 

 

Best Regards

Jianpeng Li

DataNinja777
Super User
Super User

Hi @jakewatson ,

Is there any reason that your Lead table and the Deal table have to be in separate tables?  I would have imagined that the Lead table and your Deal table can be combined using a unique identifier key.  Is this assumption correct?  In that case, I would combine the Deal table and Lead table in one table and show the lead date and deal date side by side to enable the use of sumxing over the table for the duration of lead being converted into a deal having the calendar dimension table as a disconnected table for flexible duration analysis. I've created a dummy data to demonstrate the duration analysis that can be done.  From your descriptions I guess that your cut off dates for counting is calendar months instead of number of days which have elapsed, so for example, 5/30 and 6/1 are in the different month despite it is less than 30 days.  

To tackle a issue like this, for example, you can write a formula like below to produce the visualization as shown below which shows how many calendar month over total months to deal completion has elapsed since the lead was created for each of the given point in time.  

DataNinja777_1-1717833816948.png

Which will give you an output like below (I repeat again, which shows how many calender months out of the time to deal completion has elapsed at any given point in time.)   

 

DataNinja777_2-1717833927183.png

 

I attach an example pbix file.  The solution above may not be in line with your exact output requirement, but hopefully it will provide some idea about how to tackle a duration and time related analysis using a disconnected calendar table.    

 

Best regards,

TomMartens
Super User
Super User

Hey @jakewatson ,

 

create a pbix file that contains sample data but reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive or Dropbox and share the link. If you are using a spreadsheet to load the sample data instead of the manual input method share the spreadsheet as well.

 

Do not forget to explain the expected result based on the sample data.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.