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.
In the attached file (file link below) I have the student ID, Academic Year of the Registration, Course they registered for in that AY, and the start and end dates of the particular course. This is a sample file so that I am not releasing real student data.
File: https://drive.google.com/file/d/0BxvqEMoNpMLiY1hqcnh3UkFnc0E/view?usp=sharing
Each occurrence of the student ID is considered a registration so it's a count instead of a distinct count.
What I am trying to figure out is how to take a count of registrations by student. The goal is to show the number of registrations by academic year to present in a visual like seen below. The visual shows the number of courses and the count of students by AY that completed that many courses. So in the image, you see that 1,560 students completed 1 course during what represents the current AY (2016-2017)
Note: Academic years always start July 1 and end June 30.
Solved! Go to Solution.
@JMWDBA wrote:
In the attached file (file link below) I have the student ID, Academic Year of the Registration, Course they registered for in that AY, and the start and end dates of the particular course. This is a sample file so that I am not releasing real student data.
File: https://drive.google.com/file/d/0BxvqEMoNpMLiY1hqcnh3UkFnc0E/view?usp=sharing
Each occurrence of the student ID is considered a registration so it's a count instead of a distinct count.
What I am trying to figure out is how to take a count of registrations by student. The goal is to show the number of registrations by academic year to present in a visual like seen below. The visual shows the number of courses and the count of students by AY that completed that many courses. So in the image, you see that 1,560 students completed 1 course during what represents the current AY (2016-2017)
Note: Academic years always start July 1 and end June 30.
Could you try to create a calculate table
Table = SUMMARIZE ( yourTable, yourTable[Academic Year], yourTable[Student ID], "completedCourse", COUNTA ( yourTable[Course] ) )
And then put the column completedCourse x-axis and SUM(completedCourse) as y-axis?
@JMWDBA wrote:
In the attached file (file link below) I have the student ID, Academic Year of the Registration, Course they registered for in that AY, and the start and end dates of the particular course. This is a sample file so that I am not releasing real student data.
File: https://drive.google.com/file/d/0BxvqEMoNpMLiY1hqcnh3UkFnc0E/view?usp=sharing
Each occurrence of the student ID is considered a registration so it's a count instead of a distinct count.
What I am trying to figure out is how to take a count of registrations by student. The goal is to show the number of registrations by academic year to present in a visual like seen below. The visual shows the number of courses and the count of students by AY that completed that many courses. So in the image, you see that 1,560 students completed 1 course during what represents the current AY (2016-2017)
Note: Academic years always start July 1 and end June 30.
Could you try to create a calculate table
Table = SUMMARIZE ( yourTable, yourTable[Academic Year], yourTable[Student ID], "completedCourse", COUNTA ( yourTable[Course] ) )
And then put the column completedCourse x-axis and SUM(completedCourse) as y-axis?
That worked like a charm!!!! THANK YOU!!!!!
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |