Sum a column and divide by the count of unique entries in another column
I have a dataset around training. Each row represents a training course taken by an employee, so each employee will have many rows (1 for each training course taken).
One of the columns is training hours.
I would like to make a single-value card that shows average training hours per employee. In excel, I would sum the training hours column and divide by the count of distinct strings in the employee email column. I haven't found a way to do this in Domo, though it seems simple.
Is a Beast Mode the best way to accomplish this? I tried
=SUM('Training Hours')/COUNT(DISTINCT 'Employee Email')
Best Answer
-
@tom_rotchadl Your thought process is correct, though you have a couple syntax issues in your formula:
- The equals sign is not needed in Domo, which is slightly different than Excel
- The Training Hours and Employee Email fields are being read as text values because they are surrounded by singled quotes. You will need to change the single quotes to the ` character so that Domo knows to look for dataset fields
0
Answers
-
@tom_rotchadl Your thought process is correct, though you have a couple syntax issues in your formula:
- The equals sign is not needed in Domo, which is slightly different than Excel
- The Training Hours and Employee Email fields are being read as text values because they are surrounded by singled quotes. You will need to change the single quotes to the ` character so that Domo knows to look for dataset fields
0 -
SUM(`Training Hours`)/COUNT(DISTINCT
Employee Email
)This works, but leads to a further question.
This Beast Mode is powering a single value card. The card is on a dashboard that also contains a date filter card, which is filtering on another field
Completion Date
.I would like the single value card to display the average hours per employee within the dates filtered for.
But, many rows are filtered out by date. Instead of hours/# of employees, it becomes hours/# employees that completed courses within the selected date period. For instance, if I filter for Past 30 days, many employees did not complete any training in the last 30 days and are not included in the calculation.
I could just hard code the # of employees into the Beast Mode, but of course it is fluctuating all the time and not reasonable to go into the Beast Mode to change manually.
I would like the date filter card to hit
Training Hours
but notEmployee Email
. I know I can protect the entire single value card from filters, but is there a way to allow filters for part of the beast mode formula but block it for another part?Or, another idea for how to achieve this functionality?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive