cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dallen93
Helper I
Helper I

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?

 

UsernameCourse TypeCompletedRequired
ZZZZClassroom01
ZZZZOnline00
ZZZZOnline11
ZZZZOnline11
XXXXOnline10
XXXXOnline11
XXXXClassroom11
XXXXOnline01
1 ACCEPTED SOLUTION

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"

 

exam.PNG

 

 

and you will get the result.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

11 REPLIES 11
dallen93
Helper I
Helper I

@parry2k & @Zubair_Muhammad

 

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

 

UsernameCourse TypeCompletedRequiredTraining Plan Completed?
ZZZZClassroom011
ZZZZOnline001
ZZZZOnline111
ZZZZOnline111
XXXXOnline100
XXXXOnline110
XXXXClassroom110
XXXXOnline010

 

With the solutions you have provided I think I'm close. Would love to hear what your ideas/solutions are.

Hi @dallen93

 

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]
    )
)
Regards
Zubair

Please try my custom visuals


@Zubair_Muhammad 

 

I believe this worked! Do you know how I could retrieve the username of the users to validate whether or not the logic worked?

parry2k
Super User III
Super User III

Hey @dallen93

 

Is this the result you are looking for?

 

exam.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k - Thanks for the response!

 

This is exactly what I'm looking for. How did you do it?

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"

 

exam.PNG

 

 

and you will get the result.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

 Although there are other ways to do it as well, without creating measures  but I prefer this way.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

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 ) )
)
Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors