Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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            Docum...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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" )
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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