cancel
Showing results for
Did you mean:
dphillips 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

## 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]
) ```

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

Proud to be a Datanaut! Kudos Help Connect with me on Linkedin. Feel free to email me with any of your BI needs.

3 REPLIES 3 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]
) ```

Proud to be a Datanaut! Kudos Help Connect with me on Linkedin. Feel free to email me with any of your BI needs.

dphillips 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

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

Proud to be a Datanaut! Kudos Help 