Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Datanaut!
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
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.
Course Name | Course Status | |
User1@xyz.com | No Course Taken | |
User2@xyz.com | Course B | Not Attempted |
User2@xyz.com | Course D | Not Attempted |
User2@xyz.com | Course C | In Progress |
User2@xyz.com | Course A | In Progress |
User3@xyz.com | Course A | In Progress |
User4@xyz.com | Course A | In Progress |
User5@xyz.com | Course A | Completed |
User5@xyz.com | Course C | Completed |
User5@xyz.com | Course D | Completed |
User5@xyz.com | Course A | Completed |
User6@xyz.com | No Course Taken | |
User7@xyz.com | Course A | In Progress |
User7@xyz.com | Course C | Completed |
User7@xyz.com | Course D | Completed |
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 taken | Number of Users |
0 course | 2 users |
1 course | 2 users |
2 courses | 0 user |
3 courses | 1 user |
4 courses | 2 users |
Expected graph
+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
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |