Home Forums Power Pivot Calculating the average salary of an employee in the last five years

This topic contains 13 replies, has 4 voices, and was last updated by  tomallan 1 year, 2 months ago.

Viewing 14 posts - 1 through 14 (of 14 total)
• Author
Posts
• #9401

Mathsdeb
Participant
• Started: 4
• Replies: 11
• Total: 15

Dear all

I need by using a DAX formula to calculate the salary  average by employee in the last five years

The result expected is in the sheet “RESULT_EXPECTED” in the attached sample

I have tried to use the DAX formula AVERAGEX but i don’t know how to obtain the correct result

###### Attachments:
You must be logged in to view attached files.
#9403

stevewingjr
Participant
• Started: 2
• Replies: 9
• Total: 11

Hi there,

Just to clarify a few things in your example data: Your [Year] data has  3 “2017” entries each for both of your [Employee] entries. Does this imply [Salary] is per job? Or are these amounts annual salaries that have just been duplicated in the data for 2017?

If [Salary] is annual, and is the 2017 data is just duplicated, isn’t that throwing off your AVERAGE([Salary]) calculation amount desired on your “Result_Expected” page? As it’s calculated, your “Result_Expected” would skew it closer to the [Year]”2017″, [Salary] values.

One other question about the “last five years” – [Employee] “Dupont” has [Year]”2008″ and [Year]”2009″ data. This data would typically be filtered out from the the last 5 years of [Salary] for [Year]”2017″  (the AVERAGE([Salary]) of the last 5 years for [Employee]”Dupont” would then be 1650). Or are you wanting to see the average of the last 5 years WORKED? For [Employee]”Dupont”, this would then include [Year]”2008″,  [Year]”2009″ and [Year]”2017″ data.

Thanks,

Steve

#9406

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

Mathsdeb,

Steve raises excellent and thoughtful points!

How would you answer? Also, do you want to suppress the average at the grand total level?

Tom

#9408

Mathsdeb
Participant
• Started: 4
• Replies: 11
• Total: 15

I have done some copies of the lines without modify the year. I am sorry.

Please find attached the sample modified.

I need an average of the salary by employee from the last 5 years.

Please let me know if you have others questions about the attached sample. I can modify it again if there are again some troubles.

Thank you very much for your help

###### Attachments:
You must be logged in to view attached files.
#9410

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

Consider the following:

First, a measure to establish the data year:

`Last Data Year:=MAXX ( ALL ( Data ), Data[Year] )`

Then the measure for average salary over last 5 years:

``````Avg Emp Sal (Last 5 Yrs) :=
IF (
HASONEVALUE ( DATA[Employee] ),
AVERAGEX (
FILTER ( Data, Data[Year] >= ( [Last Data Year] - 4 ) ),
DATA[Salary]
)
)``````

The HASONEVALUE is only used to suppress the grand total row.

To see the correct results, you will need to refresh all of the data in the Power Pivot window.

Attached is a solution in an Excel 2016 workbook.

###### Attachments:
You must be logged in to view attached files.
#9412

Mathsdeb
Participant
• Started: 4
• Replies: 11
• Total: 15

Thank you very much Tomallan for your help

It works perfectly!

Best regards

#9413

eisayev
Participant
• Started: 1
• Replies: 7
• Total: 8

Hi Tom Allan

I have added the year of 2018 for dupont (salary 2000) and tried to solve problem.

The function in “Last Year” was desgned to bring the max year with “ALL” function but I changed that to

=MAXX(DATAEmp;DATAEmp[Year])

Because I want to average for each employee last 5 years.

But, after changeing “Last Year” function the later function calculated all rows. For example:

Dupont – 1633.33

Tony – 1116.67

Can you tell us why average formula calculated all rows and how it can be achieved to calculate each employee for their las 5 years?

#9414

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

eisayev,

Since the answers I received after making the changes were not consistent with your results, the reason for your case is hard to exactly state.

The general issues you *could* have after removing the ALL would be inconsistencies based on the shape of your pivot and/or inconsistencies based on your data model.

Can you attach the workbook you are using with the different results for Dupont and Tony?

Tom

#9416

eisayev
Participant
• Started: 1
• Replies: 7
• Total: 8

I upload the sample file. Highlighted with yellow color the problem I encounter

###### Attachments:
You must be logged in to view attached files.
#9418

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

Eisayev,

What version of Excel are you using: 2013 or 2016?

#9419

eisayev
Participant
• Started: 1
• Replies: 7
• Total: 8

I use Excel 2016

#9420

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

eisayev,

Using a single table data model sometimes forces formulas to be complex when requirements are special. For example, the formula below will work in a single table model such as the one you attached. However, the attached workbook shows how the same results could be obtained with separate tables for employees and salaries with just calculated columns.

``````Avg Salary Last 5 Years by Employee:=IF (
HASONEVALUE ( DATAEmp[Employee] ),
VAR vEmp =
VALUES ( DATAEmp[Employee] )
VAR vLastYear =
CALCULATE (
MAX ( DATAEmp[Year] ),
FILTER ( ALL ( DATAEmp ), DATAEmp[Employee] = vEmp )
)
RETURN
AVERAGEX (
FILTER ( DATAEmp, DATAEmp[Employee] = vEmp && DATAEmp[Year] >= vLastYear - 4 ),
DATAEmp[Salary]
)
)``````

An easier way could be done in either Excel 2016 or 2013 by using a multi-table data model. Please see attached workbook.

###### Attachments:
You must be logged in to view attached files.
#9426

eisayev
Participant
• Started: 1
• Replies: 7
• Total: 8

The las answer you wrote seems a little complex to me but I will try my best to understand your formula.

On the other hand I tried to solve the problem above and seems I found solution. Here is formula:

``````=IF(HASONEVALUE(DATA2[Employee]);
AVERAGEX(
FILTER(DATA2;
DATA2[Year]>=(MAX(DATA2[Year])-4)
);
DATA2[Salary]
))
``````

One thing I realised while writing formula is;
When I use “Max Year” measurment inside “Average” measurment the formula does not give answer but when I try to write it manually inside formula it gives correct result.

Thank you for your effort. Your solutions really help me to understand DAX.

###### Attachments:
You must be logged in to view attached files.
#9432

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

eisayev,