How do you calculate a percentage using two different count(distinct) beast mode calculations?

D_Markley34
D_Markley34 Member
in Datasets

I have a data set that contains the following columns, training id, employee id, training question id (each training contains multiple questions), training completion status (i.e., passed, failed, to do).

We have this beast mode calculation to identify the number of employees that have passed the training:

case when `training completion status` = 'Passed' then count(distinct training id, employee id) end

We have this beast mode calculation to identify the number of total trainings assigned to employees:

count(distinct training id, employee id)

We need to calculate percent of employees who passed the trainings. We have tried the following calculation and it returns nothing:

(case when `training completion status` = 'Passed' then count(distinct training id,employee id) end )/(count(distinct training id, employee id))

Unfortunately, we cannot use an ETL to summarize the data at the training employee level prior to building the card.

Thanks in advance

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @D_Markley34 The main issue appears to be due to the syntax of your distinct counts. The Count function only expects one argument, so it does not know how to interpret the Training ID and Employee ID within the count. In order to take a distinct count of a combination of values from multiple columns, you will need to concatenate them together into a single string value like this: COUNT(DISTINCT CONCAT(`Training ID`,`Employee ID`))

    Secondarily, you will also likely encounter problems due to having an aggregate function inside your case statement because it causes granularity issues. You can get around this by wrapping the count around the entire case statement. Your final percentage should look like this:

    COUNT(DISTINCT case when `training completion status` = 'Complete' then CONCAT(`Training ID`,`Employee ID`) end)/COUNT(DISTINCT CONCAT(`Training ID`,`Employee ID`))

  • D_Markley34
    D_Markley34 Member
    Answer ✓

    Thanks MichelleH that works.

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @D_Markley34 The main issue appears to be due to the syntax of your distinct counts. The Count function only expects one argument, so it does not know how to interpret the Training ID and Employee ID within the count. In order to take a distinct count of a combination of values from multiple columns, you will need to concatenate them together into a single string value like this: COUNT(DISTINCT CONCAT(`Training ID`,`Employee ID`))

    Secondarily, you will also likely encounter problems due to having an aggregate function inside your case statement because it causes granularity issues. You can get around this by wrapping the count around the entire case statement. Your final percentage should look like this:

    COUNT(DISTINCT case when `training completion status` = 'Complete' then CONCAT(`Training ID`,`Employee ID`) end)/COUNT(DISTINCT CONCAT(`Training ID`,`Employee ID`))

  • D_Markley34
    D_Markley34 Member
    Answer ✓

    Thanks MichelleH that works.

Categories