Forum Discussion

kristina-brooly's avatar
kristina-brooly
Frequent Visitor
3 years ago
Solved

Can't calculate conversion rate properly

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???

 

Need Help
  • MAwwad's avatar
    MAwwad
    3 years ago

     

    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.

2 Replies

  • MAwwad's avatar
    MAwwad
    Icon for Solution Sage rankSolution Sage
    3 years ago

     

    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.