conversion rate calculation

[jakewatson]

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. 

[v-jianpeng-msft]

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 · Accepted solution]

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.  

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.)   

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]

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