conversion ratio

[Anonymous]

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)

[v-yulgu-msft]

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

[Zubair_Muhammad]

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]

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

[Anonymous]

I got a Date Table linked with the tables. Maybe it will be easier?