Sum a column and divide by the count of unique entries in another column

tom_rotchadl
tom_rotchadl Member
in Beast Mode

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

  • MichelleH
    MichelleH Coach
    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

Answers

  • MichelleH
    MichelleH Coach
    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

  • tom_rotchadl
    tom_rotchadl Member

    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 not Employee 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?

Categories