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!
