Forum Discussion
conversion ratio
Hello to all of you!
I got 1 table with my quotes and 1 table with my orders. I would like to have my conversion rate (how many quotes I converte into an order).
Quotes
Date Document N°
01/02/2018 DV001
02/03/2018 DV002
03/03/2018 DV003
Orders
Date Document N° Quote N°
02/03/2018 CO001 DV001
04/03/2018 CO002 DV002
05/03/2018 CO003 None
My ratio on March would be DV002/(CO001+CO002) -> 50%
Today, I am just able to calculate only (DV001+DV002)/(CO001+CO002) -> 100% (this ratio could be good but on 2 months)
4 Replies
Hi Anonymous
Try this...Add a new Column to your Orders Table "Quote Date".....using RELATED or LOOKUPVALUE function in DAX....
Then you can use this MEASURE to get the conversion ratio
Conversion Ratio = DIVIDE ( CALCULATE ( COUNT ( Orders[Quote N°] ), FILTER ( ALL ( Orders ), Orders[Quote N°] <> "None" && MONTH ( Orders[Quote Date] ) = MONTH ( Orders[Date ] ) && YEAR ( Orders[Quote Date] ) = YEAR ( Orders[Date ] ) ) ), CALCULATE ( COUNT ( Orders[Document N°] ), Orders[Quote N°] <> "None" ) )It's not really this ratio.
Your code is working when I choose 1 month. If I change the period and I take 2 or 3 month, it doesn't work.
I tryu to explain again my problem :
We need to know how many quotations we converted on an order and define a ratio.
So I got 2 tables ORDERS and QUOTES (rate : this is the orders created from a quote on a specific period)
ORDERS
num date ref quote quote date
1 01/02/2018 CO1 DV1 28/01/2018
2 01/03/2018 CO2 DV2 26/02/2018
3 03/03/2018 CO3 DV3 02/03/2018
4 05/03/2018 CO4 None None
QUOTES
num date quote_ref
1 28/01/2018 DV1
2 26/02/2018 DV2
3 02/03/2018 DV3
4 01/03/2018 DV4
rate Feb => we made 1 quotation (DV2) who is converted on march (CO2) so rate=0
rate march => we made 2 quotations (DV3+DV4) and one of it is converted on march (CO3) so rate = 50%
rate Trim => we made 4 quotations (DV1->4) and we converted three of it (CO1->3) so rate = 75%
Is it clearer?
I created the column (with LOOKUPVALUE) quote date. In fact I got 4 tables (quotations, archive_quotation, orders, archive_orders).
Hi Anonymous,
"My ratio on March would be DV002/(CO001+CO002) -> 50%"
What does this mean?
Could you please show us your desired output based on above 2 tables?
Regards,
Yuliana Gu
