Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need to calculate how many courses users consumed

Hi Team,

 

I am new to Power BI and figuring out how to calculate how many courses have been consumed by users. Could you please help me to figure this out?

 

I have 10 courses and 25 users. Each can take any number of courses.

 

Course A

Course B

Course C

Course D

Course E

Course F

Course G

Course H

Course I

Course J

 

Now, I need to measure how many users took all 10 courses, how many took 9 courses, ,…. How many of them not taken any course (0 course)...

 

Result (graph) I am looking for in Power BI. Below graph was done on excel.

Here, 13 users took all 10 courses, 1 user took 4 courses, 1 user took 3 courses…

 

 

I may also need to check how many took 1 to 5 course

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    No problem. On reflection, as you need to materialise a 'Number of courses taken' column for your x-axis, I think it would be simplest to stick with what we've done already, but change a couple of small things.

    Keep your original table as it is, and create a second, grouped table, that just references your original table as the source:

    let
    Source = OriginalCourseTable,
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Course Name"}),
    addCourseTaken = Table.AddColumn(repBlankNull, "courseTaken", each if [Course Status] = "No Course Taken" then 0 else 1),
    groupRows = Table.Group(addCourseTaken, {"Email"}, {{"noofTaken", each List.Sum([courseTaken]), type number}})
in
    groupRows

    This gives us the grouped table we had for your visual, and retains the original too.

     

    Now, as you have other columns, I'm assuming that you'll want to be able to slice and dice your report by different dimensions so change your measure to this:

    _noofUsers = DISTINCTCOUNT(GroupedCourseTable[Email])

     

    This now gives you the flexibility to include more columns in your grouped table so you can relate it to dimension tables and control slicing/filtering of both your original and grouped tables at the same time.

     

    Hope this all makes sense.

     

    Pete

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    Hi Anonymous ,

     

    Are you able to share an example of your data please?

    It sounds like it may be as simple as grouping your table by attendee and counting the number of courses each has taken, but can't be sure without seeing how the table is structured.

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks Pete for helping out. Here is the sample data I could provide to you. Hope you can give some idea based on this.

       

      EmailCourse NameCourse Status
      [email protected] No Course Taken
      [email protected]Course BNot Attempted
      [email protected]Course DNot Attempted
      [email protected]Course CIn Progress
      [email protected]Course AIn Progress
      [email protected]Course AIn Progress
      [email protected]Course AIn Progress
      [email protected]Course ACompleted
      [email protected]Course CCompleted
      [email protected]Course DCompleted
      [email protected]Course ACompleted
      [email protected] No Course Taken
      [email protected]Course AIn Progress
      [email protected]Course CCompleted
      [email protected]Course DCompleted

       

      Now, from above data, I want to know how many have not taken any course. In above case it is 2 users. 

      How many took just 1 course - In above case 2 users (user 3 and user 4 have took just 1 course)

       

      Number of courses takenNumber of Users
      0 course2 users
      1 course2 users
      2 courses0 user
      3 courses1 user
      4 courses2 users

       

      Expected graph

       

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        +2 for clear presentation of data and expected outcomes.

         

        If you're only using this dataset for this particular output, then I'd recommend grouping it in Power Query like this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJ0qKis0kvOz1XSUVIAYr98Bef80qLiVIWQxOzUPKVYHYg6IyR1UAVOYOUlCo4lJam5BSWpKfgUu5Ci2BnI9MxTCCjKTy9KLS7Gp9QRh1Jj4pWaEK/UFLtS5/zcgpxUZE9hUehMrEIXYhVit9qMyBg1J97bWJRi9w0WhWi+iQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, #"Course Name" = _t, #"Course Status" = _t]),
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Course Name"}),
    addCourseTaken = Table.AddColumn(repBlankNull, "courseTaken", each if [Course Status] = "No Course Taken" then 0 else 1),
    groupRows = Table.Group(addCourseTaken, {"Email"}, {{"noofTaken", each List.Sum([courseTaken]), type number}})
in
    groupRows

         

        I've taken the original example table then added these steps (ignore 'repBlankNull'):

        1) addCourseTaken = Added new column ('courseTaken') that assigns a zero where [Course Status] = 'No Course Taken', otherwise a one.

        2) groupRows = Grouped the table on [Email] with an aggregate column of SUM of [courseTaken].

         

        Once sent to the data model, you just need to add a measure like this:

        _noofUsers = COUNTROWS(courseTable)

         

        And set up your visual like this:

         

        If you need the original dataset for other calculations etc. then you'd actually want to do this all in DAX, but it's slightly more complicated so I'll wait to see whether this method works for you.

         

        Pete