topic Re: Calculating sales quote/win rates in DAX Commands and Tips

Calculating sales quote/win rates

YevD — Mon, 17 May 2021 16:58:51 GMT

Hello, I would love some help on the the problem below.

My table contains a "status" column which as 11 possible text values. I need to calculate the following rates:

quote = count of 4 ststuses / count of all statuses
hit = count of 2 statuses / count of all statuses
quoted hit = quote / hit

I realize that the first 2 rates are basicallly identical clacs (different filter/if logic), so I can derrive the formula for one from the other. Just wanted to present the whole picture. Unfortunatelly I am unable to share any data, so hoping that someone will be kind enough to help me with the DAX syntax / calculation logic.

Many thanks,

Yev

Re: Calculating sales quote/win rates

PaulOlding — Mon, 17 May 2021 17:25:33 GMT

Something like...

Quoted hit =

VAR _QuoteStatus = CALCULATE(COUNTROWS(Table), Table[Status] IN (....))

VAR _HitStatus = CALCULATE(COUNTROWS(Table), Table[Status] IN (....))

VAR _AllStatus = CALCULATE(COUNTROWS(Table), REMOVEFILTERS(Table[Status]))

RETURN

DIVIDE(_QuoteStatus, _HitStatus)

* Note: I'm not using _AllStatus in the result. Trying to remember my school days Maths but I think it cancels out. If that's not right you can add a couple of extra steps to DIVIDE(_QuoteStatus, _AllStatus) & DIVIDE(_HitStatus, _AllStatus)

Re: Calculating sales quote/win rates

Jihwan_Kim — Mon, 17 May 2021 17:30:35 GMT

Hi, YevD

I tried to create a sample pbix file based on the explanation.

please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

quote measure =

COUNTROWS (

FILTER (

'Table',

'Table'[Status] = "A001"

|| 'Table'[Status] = "A003"

|| 'Table'[Status] = "A005"

|| 'Table'[Status] = "A006"

)

hit measure =

COUNTROWS (

FILTER (

'Table',

'Table'[Status] = "A002"

|| 'Table'[Status] = "A007"

)

quoted hit measure =

DIVIDE( [quote measure], [hit measure])

https://www.dropbox.com/s/4z0t8lpu4vpsgnu/yevd.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Calculating sales quote/win rates

YevD — Mon, 17 May 2021 19:23:25 GMT

Thank you Jihwan! I did not run your method, but after learning more about my issue it looks like it would have worked as well.

Re: Calculating sales quote/win rates

YevD — Thu, 20 May 2021 20:03:20 GMT

Hi Jihwan_Kim,

I just tried your code and looks like the quote and hit measures return the count of the statuses I am interest in. Can you please help me out with the second part of the code where we need to divide this count by the total count to get % rate?

Thanks,

Yev