Best way to group countIf or sumIf results?


I’m hoping that someone might be able to help me. I’m trying to count the number of users that have completed all three courses in this table, the result will then be displayed in a KPI visualisation.

data table

As I couldn’t use sumIf to calculate the status because it is a string, I used parseInt to convert the string to an integer. I’ve tried many ways to try and count or sum a result but I cannot figure it out.

Can anyone provide any tips / guidance?

Thank you,

Hi Robert,
You can use level aware aggregate to achieve this. Below are the steps to use it for any number of courses.

  1. Find course completed by each user
    coursecompletedbyuser: sumOver(CompletedInt,[Name],PRE_FILTER)
    2.Find total number of courses
    total courses: distinctCountOver(Course, [],PRE_FILTER)

  2. Calculate CorseCompletions percent by user
    coursecompletionpercent: {coursecompletedbyUser_PRE_FILTER}/{total courses}

  3. Count the users with 100% completion
    userwith100percentcompletion: distinct_countIf(Name,coursecompletionpercent =1)

  4. Put it all in one formula:
    distinct_countIf(Name,sumOver(CompletedInt,[Name],PRE_FILTER)=distinctCountOver(Course, [],PRE_FILTER) )

1 Like

We have an upcoming ask the expert session on level aware aggregates. See the invite here: Ask the Expert - Level Aware Aggregates (April 1st at 11:30am-12pm PT). If you aren’t able to join then we will also post the session in this section of the community in the coming week: Ask the Expert - Amazon QuickSight

Hello n_vetri,

Thank you for you thorough advise, I will try your solution shortly I have just joined the exert session :slight_smile:

Thank you so much for replying to my post.

Kind Regards

Hello n_vetri,

Thank you for your detailed advise, your solution works perfectly. I understand the level aware aggregates now, the expert session was great too.

Many thanks for your time and assistance today.

Kind Regards