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.
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.
Although there are other ways to do it as well, without creating measures but I prefer this way.
Good solution by parry2k
Just another way of doing this
Go to modelling tab and create new table with this formula
NewTable = ADDCOLUMNS ( 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] ) ), "Is Completed", VAR isCompleted = [Total Required] - [Total Completed] RETURN IF ( isCompleted = 0, UNICHAR ( 10003 ), UNICHAR ( 215 ) ) )parry2k - Thanks again for the solution.
If I want to display the count of the users who have completed their training plan versus those who have not completed their training plan, how would I go about doing that?
What I'm looking to do is to have 3 cards:
# of users who completed training plan --> 1
# of users who have not completed training plan --> 2
% of users who have completed training plan --> 50%
Basically, I want to count the number of checks and divide that by the sum of checks and x's.
Recommended way would be if you have another tables with users, if not, it can be easily created in powerbi
I do have another table with users, but I'm not sure how to apply the measures you've provided me to the list of users that I have.
