cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Conversion calculation using dynamic table

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. 

yang6406_2-1601587865457.png

 

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. 

 

yang6406_3-1601588293352.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: PivotTable conversion calculation

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:

Screenshot 2020-10-07 161959.pngScreenshot 2020-10-07 162011.png

For the related .pbix file, see attachment pls.

Saludos
Kelly

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

View solution in original post

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: Conversion calculation using dynamic table

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.

Highlighted
Community Support
Community Support

Re: Conversion calculation using dynamic table

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!

 

Highlighted
New Member

Re: Conversion calculation using dynamic table

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

Highlighted
Community Support
Community Support

Re: PivotTable conversion calculation

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:

Screenshot 2020-10-07 161959.pngScreenshot 2020-10-07 162011.png

For the related .pbix file, see attachment pls.

Saludos
Kelly

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

View solution in original post

Highlighted
New Member

Re: Conversion calculation using dynamic table

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors