Beast Mode Help: Calculating Hit Rate (Projects & Quotes)

swagner

Here is a short video overview of what I am trying to accomplish:  https://youtu.be/Dz_uXS1OGco

In simple terms the Project Hit Rate is the Count of (distinct) "Project Name" where "Convereted to Order?"='Yes' divided by the total Count of (distinct) "Project Name".

 

In simple terms the Quote Hit Rate is the Count of (distinct) "Quote Number" where "Convereted to Order?"='Yes' divided by the total Count of (distinct) "Quote Number".

 

A project might consist of multiple quotes, if any 1 of the quotes has "Convereted to Order?"='Yes'  then the project is counted as a won project in the calculation above.  Project Hit Rate = 100%

 

In the case of quotes, if say there are 4 quotes on the project and only one of them is "Convereted to Order?"='Yes' then the quote hit rate is 25%

 

My current Beast Modes:

 

Project Hit Rate:

CASE WHEN IFNULL(COUNT(DISTINCT `Project Name`),0)=0 THEN 0 ELSE CASE when `Converted to Order?`='Yes' then COUNT(`Project Name`) else 0 END   / COUNT(DISTINCT `Project Name`) END

 

Quote Hit Rate:

CASE WHEN IFNULL(COUNT(`Quote Number`),0)=0 THEN 0 ELSE CASE when `Converted to Order?`='Yes' then COUNT(`Quote Number`) else 0 END   / COUNT(`Quote Number`) END

 

I should note I am using subtotals in the table card, and the subtotals are not working.  Thanks in advance for your help!

swagner

I figured it out on my own.  Post solution in case others have the same issue.

 

I was over complicating the Beast Modes:

 

Project Hit Rate:

CASE WHEN IFNULL(COUNT(`Quote Number`),0)=0 THEN 0 ELSE Sum(case when `Converted to Order?`='Yes' then 1 else 0 end) / COUNT(DISTINCT `Project Name`) END

 

Quote Hit Rate:

CASE WHEN IFNULL(COUNT(`Quote Number`),0)=0 THEN 0 ELSE Sum(case when `Converted to Order?`='Yes' then 1 else 0 end) / COUNT(`Quote Number`) END