Can't calculate conversion rate properly

[kristina-brooly]

There are two sources for this report: Orders and Payments.

I used LOOKUPVALUE to put Product names in both tables.

So here we want to calculate conversion rates (how many orders have been payed).

the report 

But calculation is not correct (I tried to calculate conversion rate manually and it doesn't match)

Conversion rate  = DIVIDE([Orders payed], [Orders amount])

 

Orders payed = CALCULATE(COUNTA('Orders'[First payment date]), USERELATIONSHIP('Calendar'[Date], 'Orders'[First payment date]))

 

Orders amount = CALCULATE(DISTINCTCOUNTNOBLANK('Orders'[Order ID]))

 

First payment date =

var N='Orders'[№ Order]

return

CALCULATE(MIN('Payments'[PaymentDate]), 'Payments'[№ order]=N)

 

Its seems to me that First payment date is not correct

What am I doing wrong???

[MAwwad · Accepted solution]

It looks like the issue might be with the 'First payment date' calculation. The 'First payment date' measure is using a variable to calculate the minimum payment date for each order in the 'Orders' table, but it's not clear how the 'Payments' table is related to the 'Orders' table.

To calculate the 'First payment date' measure correctly, you need to make sure that there is a relationship between the 'Orders' table and the 'Payments' table based on the order ID column. Once you have established the relationship, you can use the RELATED function to retrieve the payment date from the 'Payments' table. Here's an updated measure that should work:

First payment date = CALCULATE( MIN('Payments'[PaymentDate]), USERELATIONSHIP('Orders'[Order ID], 'Payments'[Order ID]) )

Once you have corrected the 'First payment date' measure, you should be able to calculate the conversion rate correctly using the measures you have defined.

[kristina-brooly]

Thank you so much! It worked