Calculation of difference between salary and average salary for a given position

[ThomasKieny]

Hi everyone, 

I'm new to Power BI and DAX and would need a little help on a calculation that looks pretty basic... 
I have a table that looks like this: 

I want to create a column, in which I'll have the difference between the employee's salary and the average salary for this position (column [Position]). Then I could rapidly see if this employee has a salary below or above the average for this job. 

Thank you for your help !

[Dangar332 · Accepted solution]

Hi, @ThomasKieny 

for difference bw salary and average
try below column formula

for difference = 
var a = AVERAGEX(FILTER('Table (3)','Table (3)'[position]=EARLIER('Table (3)'[position])),'Table (3)'[salary])
var b = ABS('Table (3)'[salary] - a)
return
b

for above and below

above and below = 
var a = AVERAGEX(FILTER('Table (3)','Table (3)'[position]=EARLIER('Table (3)'[position])),'Table (3)'[salary])
RETURN
IF('Table (3)'[salary]<a,"below","above")

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas

Average salary of position = calculate(average(Data[Salary]),filter(data,data[position]=earlier(data[position])))

Status = if(data[Salary]>data[Average salary of position],"Above",if(data[Salary]<data[Average salary of position],"Below","Same"))

Hope this helps.

[Dangar332 · Accepted solution]

Hi, @ThomasKieny 

for difference bw salary and average
try below column formula

for difference = 
var a = AVERAGEX(FILTER('Table (3)','Table (3)'[position]=EARLIER('Table (3)'[position])),'Table (3)'[salary])
var b = ABS('Table (3)'[salary] - a)
return
b

for above and below

above and below = 
var a = AVERAGEX(FILTER('Table (3)','Table (3)'[position]=EARLIER('Table (3)'[position])),'Table (3)'[salary])
RETURN
IF('Table (3)'[salary]<a,"below","above")

[rubinboer]

hi @ThomasKieny   

if you want to add a column you can do this:

Compare to Avg = IF( YourTable[Salary] > AVERAGE(YourTable[Salary]), "Above", "Below")

The above will not yield the result, i missed the part of salary avg per job. the below will work:

Compare to Avg =

VAR AvgPerPosition =

    AVERAGEX(  

        FILTER(

        //here the table called "Salaries whic is your table" is filtered to add only rows with teh same position

            Salaries, [Position] = EARLIER([Position])

        ),

        [Salary]

    )

RETURN

SWITCH(

    TRUE(), Salaries[Salary] = AvgPerPosition, "The Same",

    Salaries[Salary] > AvgPerPosition, "Above",

    Salaries[Salary] < AvgPerPosition,"Below"

)

Result: