Skip to content

    • Beast Mode

    Beast Mode

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

    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

    • 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

    • 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
    • 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?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories