Forum Discussion
Group, sum, and compare
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?
