Beast Mode Help: Calculating Hit Rate (Projects & Quotes)
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!
Comments
-
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`)
ENDQuote 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`)
END2
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 198 Visualize
- 254 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 178 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive