Forum Discussion
Need Help with Aggregation.
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?
4 Replies
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?
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.
Thanks ChrisHaas!
I'm looking for the following table with an average Volume per ClassYear:
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.
