Conversion calculation using dynamic table

[yang6406]

Hi there,

I have a table which is organized by date, customer ID, web visit and purchase (dont worry about "show). I would like to calculate the visit to purchase conversion rate. The conversion rate has to be tied directly to individual customer, not just using sum of purchase divided by sum of visit (6/9 = 67%). In stead I am interested to see if the purchase is made by the same customer who visited the store. For example, from Jan 1 - Jan 3, the conversion should be (4/9 = 44%). The cutomer column has repeated values if the customer purchase on a different day. Assuming one customer only visits the store once and purchase once in their lifetime. 

In order to calculate true conversion, I use calculatetable to create a new table that shows the unique cutomers, visit and purchase. It is clear in the following table that from Jan 1 - Jan 3, the cutomers who visited and purchased during these three days are T1, T8, T10, and T13. I want to make this table dynamic based on the time frame the users pick. For example, if the user pick Jan 3rd, the conversion should be 100%, since two new customer who visited to the store (T10 AND T13) both purchased on the same day. Somehow, I cannot make the dynamic piece work. The table stay static. Can you help? Please feel free to critic if there is smarter way to do so. Thanks. 

[v-kelly-msft]

Hi @yang6406 ,

You can create a date table to be used as a slicer ,then create a measure to fulfill your requirement.

If possible,could you pls upload your .pbix file to onedrive business and share the link with us?Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[yang6406]

Hi @v-kelly-msft 

Thanks for your hint. I have created a date table, and created visit to purchase conversion measure. However, the conversion measure does not change as I hope when the date slicer changes. 

.pbix file is in the link below. 

• Sheet1(2) is the data file
• Test Table 3 is the caclualtetable
• Visit to purchase is in test table 3. My goal is to make visit to purchase dynamic when the date range changes. Thanks for all your help. 

https://bozzuto-my.sharepoint.com/:f:/p/xiyao_yang/EtJ6508x4_VCh_1XrvdC-VgBbiuRrIyRV-4n6bOYSpguyQ?e=...

password: 1234

[v-kelly-msft · Accepted solution]

Hello @yang6406 ,

First create a dim table as shown below:

calendar dim = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Next, create a measure as follows:

lease percentage = 
var _selectedtable1=CALCULATETABLE(VALUES('Table'[Tenant]),FILTER(ALL('Table'),'Table'[First Contact]=1&&'Table'[Date] in FILTERS('calendar dim'[Date])))
var _sumoflease=CALCULATE(DISTINCTCOUNT('Table'[Tenant]),'Table'[Tenant] in _selectedtable1,FILTER(ALL('Table'),'Table'[Lease]=1&&'Table'[Date] in FILTERS('calendar dim'[Date])))+0
var _sumoffirstcontact=CALCULATE(DISTINCTCOUNT('Table'[Tenant]),FILTER(ALL('Table'),'Table'[First Contact]=1&&'Table'[Date] in FILTERS('calendar dim'[Date])))
Return
DIVIDE(_sumoflease,_sumoffirstcontact)+0

And you'll see:

For the related .pbix file, see attachment pls.

Saludos
Kelly

Have I answered your question? Mark my position as a solution!

[yang6406]

@v-kelly-msft , thank you so much! Beautifully done. 

[lbendlin]

You are on the right track using a calculated table. All that's left to do is to put this inside a measure, with a table variable as an intermediate step. You can even re-use most of your code.