Calculate average score by time periods in Powerbi

Question

I am trying to calculate the average of students' math score across different years and visualize them in Powerbi by understanding the trends of student's average score from 2014 through 2018. I wondered what will be the most efficient way to do it. I am still new to Powerbi. I looked up DAX language and seems like it can only get an average of the whole column of math scores regardless of specific year. If it can accomplish my goal, can anyone guide me how to do it, please?

Thanks!

Excel sheet in TEXT format

YEAR    Math Score  
2014    80  
2014    43  
2014    23  
2015    21  
2015    78  
2015    42  
2015    100
2015    59  
2016    48  
2016    24  
2017    90  
2017    99  
2018    98

Have you even created the pivot table yet? Just highlight your data (including the columns), and click the pivot table wizard button. — Tim Biegeleisen
No. Include your data as text so that someone else can easily drop it into Excel. — Tim Biegeleisen
I did. But I am wondering if there is anyway I can do it in PowerBI? Are we allowed to create a pivot table in POwerbi? — Dolphine

Lukasz P. Lukasz P. · Accepted Answer · 2016-06-30T02:12:48

If I get your question, you want to get Power BI to produce a data set that looks like the below and then visualize it:

YEAR Average of Math Score

2014 49

2015 60

2016 36

2017 95

2018 98

Assuming that's right you just need to add the Math Score measure to your chart and select the average aggregate.

Like in a Pivot Table in Excel, Power BI will automatically aggregate data that is in the same category. The default aggregate is SUM, but you can select from a range of aggregates.

To look at individual students,you'd either filter (us a slicer) or add the student's name as a category in the legend property of the visual.

here's the result: https://msit.powerbi.com/view?r=eyJrIjoiMDdkMDM5ODMtMzc0YS00NWRlLWJhZTMtZGU2ZDNkZDU2Y2M1IiwidCI6IjcyZjk4OGJmLTg2ZjEtNDFhZi05MWFiLTJkN2NkMDExZGI0NyIsImMiOjV9

Download the file here: https://1drv.ms/u/s!AgPrV7zj1R9Dj8BZyjlbcG9nsIXBpQ

Calculate average score by time periods in Powerbi

1 Answers