Forum Discussion
Group, sum, and compare
Hi Everyone,
I'm trying to determine whether or not a user has completed their online training plan. In the table below you will see two users who have 4 courses, some online some classroom that comprises their overall training plan. In order to figure out what their online training plan, I'd need to filter the course type by "online" and also filter required by "1" because "0" indicates optional. The desired result to be able to show 2 cards: 1 that displays the # of users who have actually completed their training plan, 1 that displays the # of users who were planned to have completed their training plan.
With the data below, those cards would show 1 (User ZZZZ completed all of their required, online training) over 2 (the total number of users being considered. The logic I've come up with, hence the title of the post, is as follows:
- Filter by "course type = online" and "required = 1"
- Group by username
- Sum the the completed
- Sum the required
- Compare completed and required
- If these match, then the user has completed their training plan, else they have not
I feel like I've tried it all, but haven't been able to get anything to work. Can you all assist?
| Username | Course Type | Completed | Required |
| ZZZZ | Classroom | 0 | 1 |
| ZZZZ | Online | 0 | 0 |
| ZZZZ | Online | 1 | 1 |
| ZZZZ | Online | 1 | 1 |
| XXXX | Online | 1 | 0 |
| XXXX | Online | 1 | 1 |
| XXXX | Classroom | 1 | 1 |
| XXXX | Online | 0 | 1 |
Perfect, here are dax formulas for you:
1st - add measure for total required
Total Required = CALCULATE(SUM(Table2[Required]), Filter(Table2, Table2[Course Type] = "Online"))
2nd - add measure for total completed
Total Completed = CALCULATE(SUM(Table2[Completed]), FILTER(Table2, Table2[Course Type] = "Online" && Table2[Required] = 1))
3rd - add measure to check if completed
Is Completed = var isCompleted = [Total Required] - [Total Completed] return if(isCompleted=0, UNICHAR(10003), UNICHAR(215))
Add fields to a table visual and filter visual or page by course type and select "online"
and you will get the result.
11 Replies
Hey Anonymous
Is this the result you are looking for?
Perfect, here are dax formulas for you:
1st - add measure for total required
Total Required = CALCULATE(SUM(Table2[Required]), Filter(Table2, Table2[Course Type] = "Online"))
2nd - add measure for total completed
Total Completed = CALCULATE(SUM(Table2[Completed]), FILTER(Table2, Table2[Course Type] = "Online" && Table2[Required] = 1))
3rd - add measure to check if completed
Is Completed = var isCompleted = [Total Required] - [Total Completed] return if(isCompleted=0, UNICHAR(10003), UNICHAR(215))
Add fields to a table visual and filter visual or page by course type and select "online"
and you will get the result.
I've realized that I need to create a column in order to calculate the number of users who have completed their training plan. How would I achieve the column entitled "Training Plan Completed?"?
Username Course Type Completed Required Training Plan Completed? ZZZZ Classroom 0 1 1 ZZZZ Online 0 0 1 ZZZZ Online 1 1 1 ZZZZ Online 1 1 1 XXXX Online 1 0 0 XXXX Online 1 1 0 XXXX Classroom 1 1 0 XXXX Online 0 1 0 With the solutions you have provided I think I'm close. Would love to hear what your ideas/solutions are.
Hi Anonymous
With the same conditions you specified in the first post, I believe you can use this measure to count the number of users completed their plan. (Without the need to add a separate column)
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( FILTER ( TableName, TableName[Course Type] = "Online" && TableName[Required] = 1 ), TableName[Username], TableName[Course Type], "Total Completed", SUM ( TableName[Completed] ), "Total Required", SUM ( TableName[Required] ) ), [Total Completed] = [Total Required] ) )I believe this worked! Do you know how I could retrieve the username of the users to validate whether or not the logic worked?
