cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Umakh27
New Member

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

Umakh27_2-1666088357309.png

 

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…

 

Umakh27_0-1666087882403.png

 

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

1 ACCEPTED SOLUTION

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

Hi @Umakh27 ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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
User1@xyz.com No Course Taken
User2@xyz.comCourse BNot Attempted
User2@xyz.comCourse DNot Attempted
User2@xyz.comCourse CIn Progress
User2@xyz.comCourse AIn Progress
User3@xyz.comCourse AIn Progress
User4@xyz.comCourse AIn Progress
User5@xyz.comCourse ACompleted
User5@xyz.comCourse CCompleted
User5@xyz.comCourse DCompleted
User5@xyz.comCourse ACompleted
User6@xyz.com No Course Taken
User7@xyz.comCourse AIn Progress
User7@xyz.comCourse CCompleted
User7@xyz.comCourse 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

Umakh27_0-1666165292164.png

 

 

+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:

BA_Pete_0-1666167135907.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks Pete. I want to use original dataset for other calculations as well. I have other different columns in the file.

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors