cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## Count of Registrations by AY

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.

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.

1 ACCEPTED SOLUTION
Microsoft

@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.

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.

@JMWDBA

Could you try to create a calculate table

```Table =
SUMMARIZE (
yourTable,
yourTable[Student ID],
"completedCourse", COUNTA ( yourTable[Course] )
)```

And then put the column completedCourse x-axis and SUM(completedCourse) as y-axis?

2 REPLIES 2
Microsoft

@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.

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.

@JMWDBA

Could you try to create a calculate table

```Table =
SUMMARIZE (
yourTable,
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!!!!!