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 ...
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.
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?