Calculate the percentage change of an average

[dphillips]

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. 

https://docs.google.com/spreadsheets/d/1xWq6puSfJ_AAuS-xJDk0-o9yevfh0IlxboEH5Px1paU/edit?usp=sharing

Any help would be appreciated.

[parry2k · Accepted solution]

@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]
 ) 

[parry2k · Accepted solution]

@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]
 ) 

[naresh_nsn]

Hello Parry

This was helpful, just want to know how to combine all 3 measures

Moreover I have created 3 average measures for Current year, last Year and Last Year-1 with VAR, but not able to RETURN required results which i want with a single measure

BR//Naresh

[dphillips]

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?

[parry2k · Accepted solution]

@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.