cancel
Showing results for
Did you mean:
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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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:

For the related .pbix file, see attachment pls.

Saludos
Kelly

5 REPLIES 5
Highlighted
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

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

Best Regards,
Kelly

Highlighted
New Member

## Re: Conversion calculation using dynamic table

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

Highlighted
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:

For the related .pbix file, see attachment pls.

Saludos
Kelly

Highlighted
New Member

## Re: Conversion calculation using dynamic table

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021