Calculating sales quote/win rates

[YevD]

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

[PaulOlding · Accepted solution]

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)

[Jihwan_Kim · Accepted solution]

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

[YevD]

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

[YevD]

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.

[PaulOlding · Accepted solution]

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)