March 10, 2018 at 1:39 pm #9401
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 helpMarch 10, 2018 at 4:34 pm #9403
- Started: 1
- Replies: 3
- Total: 4
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.
SteveMarch 10, 2018 at 5:51 pm #9406
Steve raises excellent and thoughtful points!
How would you answer? Also, do you want to suppress the average at the grand total level?
TomMarch 10, 2018 at 8:11 pm #9408
Thank 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 helpMarch 10, 2018 at 9:40 pm #9410
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.March 11, 2018 at 12:25 pm #9412
Thank you very much Tomallan for your help
It works perfectly!
Best regardsMarch 12, 2018 at 8:44 am #9413
Hi 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
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 advanceMarch 12, 2018 at 2:27 pm #9414
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)?
TomMarch 13, 2018 at 5:04 am #9416
Thank you for reply
I upload the sample file. Highlighted with yellow color the problem I encounterMarch 13, 2018 at 5:19 am #9418
What version of Excel are you using: 2013 or 2016?March 13, 2018 at 5:22 am #9419
I use Excel 2016March 13, 2018 at 6:26 am #9420
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.March 13, 2018 at 9:05 am #9426
Thank 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.March 13, 2018 at 1:53 pm #9432
Glad to hear of your success!
You must be logged in to reply to this topic.