Need Help with Aggregation.

[kosmo]

Post is de-identified.

I'm new to PowerBI and need some help using aggregation in visualizations. I have three tables, AY (academic year), StudentList, and Volumes.  They are set up as below:

AY:

AY	Start	End
AY 2010-11	Thursday, July 1, 2010	Thursday, June 30, 2011
AY 2011-12	Friday, July 1, 2011	Saturday, June 30, 2012
AY 2012-13	Sunday, July 1, 2012	Sunday, June 30, 2013

ClassList

FirstName	LastName	Full Name	ClassYear
John	Smith	John Smith	1
Fred	Thomas	Fred Thomas	1

Volumes

AY	Student	Transcribed date	Completed volume	Subspecialty	LastName	FirstName+MI	FirstName	StudentName
AY 2014-15	SMITH XX-Student, JOHN R	Monday, January 12, 2015	1	CT AP	SMITH	JOHN R	JOHN	SMITH, JOHN

I have created a visualization like this that totals the volumes per student for a particular study type:

I am trying to create a table as below for average volume for each class year for particular study types, but the average aggregation is giving values as below.

What am I doing wrong? I probably need to use a measure, but I'm unsure of how to set it up.

As an aside, I am filtering the visualization per student. I'd like to be able to output a report in PDF that shows the data for each student separately. Is there any way to automatte/batch such a report, or do I just have to create a page per student with the same visualization to do that?

[Anonymous]

To answer your first question, yes you likely need a measure to calculate what you're looking for.  Using the Implicit measures (similar to what you would do with a standard pivot table) is not best practice, and doesn't even scratch the surface of what Power BI can do.

Can you provide what results you WANT to see in the table?  We can help you write the measure, but we need to better understand what you're trying to calculate.

To answer your 2nd question, Power BI is not the solution if you want to batch/automate reports for different students on a separate page.  You should look into using SSRS for that.

[kosmo]

Thanks ChrisHaas!

I'm looking for the following table with an average Volume per ClassYear:

[kosmo]

So I guess ot rephrase, each student is a member of a class. I want to aggregate the volumes for each class member and average that number year and per month.

Each student would be on a difference subspecialty each month. In the end, I want to see something giving me the ability to see the average R1 student's volume in a year, in a month, and in a particular subspecialty.

[kosmo]

I'm new to PowerBI and need some help using aggregation in visualizations. I have three tables, AY (academic year), StudentList, and Volumes.  They are set up as below:

AY:

AY	Start	End
AY 2010-11	Thursday, July 1, 2010	Thursday, June 30, 2011
AY 2011-12	Friday, July 1, 2011	Saturday, June 30, 2012
AY 2012-13	Sunday, July 1, 2012	Sunday, June 30, 2013

ClassList

FirstName	LastName	Full Name	ClassYear
John	Smith	John Smith	1
Fred	Thomas	Fred Thomas	1

Volumes

AY	Student	Transcribed date	Completed volume	Subspecialty	LastName	FirstName+MI	FirstName	StudentName
AY 2014-15	SMITH XX-Student, JOHN R	Monday, January 12, 2015	1	CT AP	SMITH	JOHN R	JOHN	SMITH, JOHN

I have created a visualization like this that totals the volumes per student for a particular study type:

I am trying to create a table as below for average volume for each class year for particular study types, but the average aggregation is giving values as below.

What am I doing wrong? I probably need to use a measure, but I'm unsure of how to set it up.

As an aside, I am filtering the visualization per student. I'd like to be able to output a report in PDF that shows the data for each student separately. Is there any way to automatte/batch such a report, or do I just have to create a page per student with the same visualization to do that?