cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




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! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




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! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




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! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.