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?