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 7 months, 1 week ago.

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

    Mathsdeb
    Participant
    • Started: 3
    • Replies: 11
    • Total: 14

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

    stevewingjr
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    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: 2517
    • Total: 2524

    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: 3
    • Replies: 11
    • Total: 14

    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 help

     

     

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

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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: 3
    • Replies: 11
    • Total: 14

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

    #9414

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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?

    For working out an answer to your second question, please advise what version of Excel you are using (2010, 2013 or 2016)?

    Tom

    #9416

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

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

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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: 2517
    • Total: 2524

    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

    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.

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

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    eisayev,

    Glad to hear of your success!

    Best regards,

    Tom

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.