cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dphillips Member
Member

Calculate the percentage change of an average

I have some marks for students and I wanted to find the percentage change of the average of marks from one year to the next. eg For student 113048 (See link to data below) the Current Year Average = 60. CurrentYear - 1 = 73.24. Current Year -2 = 76.69. From currentyear to currentyear - 1 this is a 22.06% fall in average scaled mark. I am trying to do this all in measures with limited success.

 

The measure below will give me the average of the current year (I have a slicer which I use for the student ids)

The average of the currentyear - 1 is the same measure with Year(now())-1.

Scld Mark Average Current = calculate(
                            AVERAGE(uncRedshift_Studentresults[Scld]),
                            Filter(uncRedshift_Studentresults,uncRedshift_Studentresults[FileYear] = Year(now())
))

I am then having trouble using a measure to get the percentage change between the two.

At the moment I have;
 
ScldMrk% = ([Scld Mark Average Previous]-[Scld Mark Average Current])/[Scld Mark Average Current]
 
but this is giving me the wrong answer of -100% or -1 depending on the data type. I can't seem to get this to work. does a measure always have to have some aggregation? Is that why I am getting the wrong answer?
Here is a link to the data. 
 
Any help would be appreciated.
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Calculate the percentage change of an average

@dphillips add following 3 measures, you can always combine all this in one if you want.

 

 

Avg Mark = AVERAGE( Sheet1[Scld Mark] )

Avg Mark Prev Year = 
VAR __prevYear = MAX( Sheet1[FileYear] ) - 1
RETURN
 CALCULATE( [Avg Mark], Sheet1[FileYear] = __prevYear )

Avg Mark Change % = 
DIVIDE( [
Avg Mark Prev Year] - [Avg Mark],
 [Avg Mark]
 ) 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

Super User
Super User

Re: Calculate the percentage change of an average

@dphillips but it depends on how you want to view your data, if you have year in your visuals then it will take year in filter context of this measure.

 

For example, use matrix visual, put student on rows, years on columns and then these 3 measures on values, you will see the change of each year from prev year. All depends on the need, you can surely change the measure as per your need.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
Super User
Super User

Re: Calculate the percentage change of an average

@dphillips add following 3 measures, you can always combine all this in one if you want.

 

 

Avg Mark = AVERAGE( Sheet1[Scld Mark] )

Avg Mark Prev Year = 
VAR __prevYear = MAX( Sheet1[FileYear] ) - 1
RETURN
 CALCULATE( [Avg Mark], Sheet1[FileYear] = __prevYear )

Avg Mark Change % = 
DIVIDE( [
Avg Mark Prev Year] - [Avg Mark],
 [Avg Mark]
 ) 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

dphillips Member
Member

Re: Calculate the percentage change of an average

Thanks for the info. Just a question about the first line.. 

Avg Mark = AVERAGE( Sheet1[Scld Mark] )

Won't this give me the average over all years, not just the most current year? Don't I need to filter this in some way to get just the most current year?

Super User
Super User

Re: Calculate the percentage change of an average

@dphillips but it depends on how you want to view your data, if you have year in your visuals then it will take year in filter context of this measure.

 

For example, use matrix visual, put student on rows, years on columns and then these 3 measures on values, you will see the change of each year from prev year. All depends on the need, you can surely change the measure as per your need.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 879 guests
Please welcome our newest community members: