Quote to Sales Conversion Analyses

[HabibAdil]

Hi Everyone,

Please can you help me with the following?

I am trying to do quote conversion analyses. I want to know how many of the quotes we sent were converted into Sales Orders (SO) (I will also be doing this for values, how much quoted value turned into sales). There are two cases here. If a quote is directly converted into SO (called quick loaded) then there is a direct link between the quote and the SO and in the Quotes table, a SO record is made in the SO_No field. However, if a quote is not quick loaded then we have to create an opportunity (Opportuinty_GUID) which is linked to a quote. The opportunity can be linked to SO hence there is a relationship created between quote to SO. 

The issue I have is, I have many versions of the same quote. One version of the same quote might have been quick loaded while the other one not or connected to SO through opportunity. The SO will repeat for both opportunity and quick loaded.  If one version of a quote is quick loaded and the other is related to Opportunity and both are related to the same SO, I want the quick loaded version in my analyses and the other one being ignored. However, I do want to repeat the SO if the different versions of the same quote are related to different SOs.

In case you are wondering if a quote is quick loaded (won) then what is the need for next version. We may not have won the whole quote and on our ERP we cannot select which items on the quote are won and which ones not. Later we might win some more items from the same quick loaded quote, but we cannot quick loaded again as it will overwrite the first Quote-SO relationship so instead we create opportunity which is linked to quote and the opportunity is linked to SO.

Many thanks in advance!!!!

[v-lionel-msft]

Hi @HabibAdil ,

"The issue I have is, I have many versions of the same quote. One version of the same quote might have been quick loaded while the other one not or connected to SO through opportunity. ..."

Please give a more specific example data, maybe we can use DAX to solve your problem.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HabibAdil]

Hi @v-lionel-msft 

Thank you for your response. Please find below a sample data. 

[v-lionel-msft · Accepted solution]

Hi @HabibAdil ，

I created a model like this:

Then, I created a meassure:

Q_V = 
CALCULATE(
    MAX(Quotes[Quote Version]),
    FILTER(
        SO,
        SO[SO_NO] <> BLANK()
    )
)

Then, I got "Quick loaded outcome wanted":

But for "Not Quick loaded outcome wanted", you can't get the correct result from the model:

So you need to create a new data model, like this:

Then, I created a measure:

Measure = 
VAR x = 
CALCULATE(
   DISTINCTCOUNT('Quotes (2)'[SO_NO]),
    ALLEXCEPT(
        'Quotes (2)',
        'Quotes (2)'[Quote No]
    ),
    ALL('SO (2)')
)
RETURN 
IF(
    x = 1 && MAX('Quotes (2)'[Opportunity_GUID]) = MAX('SO (2)'[Opportunity_GUID]) && MAX('Quotes (2)'[Opportunity_GUID]) <> BLANK(),
    MAX('Quotes (2)'[Quote Version]),
    BLANK()
)

Then, added a filter and you could get the result:

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.