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 3 months, 2 weeks ago.

AuthorPosts

March 10, 2018 at 1:39 pm #9401
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
Thank you in advance for your help
Attachments:
You must be logged in to view attached files.March 10, 2018 at 4:34 pm #9403Hi 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
March 10, 2018 at 5:51 pm #9406Mathsdeb,
Steve raises excellent and thoughtful points!
How would you answer? Also, do you want to suppress the average at the grand total level?
Tom
March 10, 2018 at 8:11 pm #9408Thank you Steve and Tomallan for your reply
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.March 10, 2018 at 9:40 pm #9410Consider 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.March 11, 2018 at 12:25 pm #9412Thank you very much Tomallan for your help
It works perfectly!
Best regards
March 12, 2018 at 8:44 am #9413Hi Tom Allan
I have question about this query.
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?
Thank you in advance
March 12, 2018 at 2:27 pm #9414eisayev,
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?
For working out an answer to your second question, please advise what version of Excel you are using (2010, 2013 or 2016)?
Tom
March 13, 2018 at 5:04 am #9416Thank you for reply
I upload the sample file. Highlighted with yellow color the problem I encounter
Attachments:
You must be logged in to view attached files.March 13, 2018 at 5:19 am #9418Eisayev,
What version of Excel are you using: 2013 or 2016?
March 13, 2018 at 5:22 am #9419I use Excel 2016
March 13, 2018 at 6:26 am #9420eisayev,
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 multitable data model. Please see attached workbook.
Attachments:
You must be logged in to view attached files.March 13, 2018 at 9:05 am #9426Thank you for reply,
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.March 13, 2018 at 1:53 pm #9432eisayev,
Glad to hear of your success!
Best regards,
Tom

AuthorPosts
You must be logged in to reply to this topic.