Sum and Average Salary by Category

[avou26]

Hello! I am trying to create a visual that shows the average salary per each position type. My data set has each payment that was made over the year to each person. Is there a dax that will total the amount paid out and then average by position? 

[v-binbinyu-msft · Accepted solution]

Hi @avou26 ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Sum Amount Per Job = SUM('Table'[Amount])

Average Per Job =
VAR cur_job =
    SELECTEDVALUE ( 'Table'[Earnings Job Description] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Earnings Job Description] = cur_job )
VAR a =
    SUMX ( tmp, [Amount] )
VAR b =
    COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[Employee Number] ), tmp ) )
RETURN
    DIVIDE ( a, b )

3. add a Clustered column chart with field and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-binbinyu-msft · Accepted solution]

Hi @avou26 ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Sum Amount Per Job = SUM('Table'[Amount])

Average Per Job =
VAR cur_job =
    SELECTEDVALUE ( 'Table'[Earnings Job Description] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Earnings Job Description] = cur_job )
VAR a =
    SUMX ( tmp, [Amount] )
VAR b =
    COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[Employee Number] ), tmp ) )
RETURN
    DIVIDE ( a, b )

3. add a Clustered column chart with field and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @avou26 

If i understand you correctly

Sum of salary

Salary_by_category = sumx(values('yourtable'[earnings job description]), Sum([amount])

Average

AVG_by_category = Averagex(values('yourtable'[earnings job description]), average([amount])

if not 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[avou26]

Sorry about that! Thank you for your response @Ritaf1983 . Here is some sample data- i realize with my initial question that i didn't include that the employees have unique ID numbers. I would like to sum the amount per each job description category, and average it using the amount of employees(hopefully with the id number) to see what the average pay per each category is.  At the end I hope to create a bar chart visual that shows the topN paying positions in the organization based on average pay. The issue i keep running into is it is only totalling all positions and averaging based on every line, not the unique employee number

Employee Number	Amount	Earnings Job Description
11183	1,761.20	ACCOUNT CLERK
11218	1,761.20	ACCOUNT CLERK
11218	1,761.20	ACCOUNT CLERK
11218	1,761.20	ACCOUNT CLERK
11183	1,749.25	ACCOUNT CLERK
11183	1,736.43	ACCOUNT CLERK
11561	1,717.10	ACCOUNT CLERK
11561	1,717.10	ACCOUNT CLERK
10738	2,941.96	AUDITOR
10738	2,922.61	AUDITOR
38388	2,904.00	AUDITOR
38388	2,904.00	AUDITOR
38388	2,904.00	AUDITOR
48980	214.90	CASEWORKER
48980	214.90	CASEWORKER
47590	214.90	CASEWORKER
47590	214.90	CASEWORKER
47590	214.90	CASEWORKER
47590	214.90	CASEWORKER
39041	214.88	CASEWORKER
46927	214.38	CASEWORKER
47079	214.38	CASEWORKER
46198	213.22	CASEWORKER
46927	213.22	CASEWORKER
38907	1,950.10	FISCAL OFFICER
49859	1,930.11	FISCAL OFFICER
49859	1,792.25	FISCAL OFFICER
10260	1,627.15	FISCAL OFFICER
10260	1,627.15	FISCAL OFFICER
49859	1,605.14	FISCAL OFFICER
10260	1,603.91	FISCAL OFFICER

[Ritaf1983 · Accepted solution]

Hi @avou26 

To achieve a goal I used 2 measures.

for sum :

Sum_Salary = sum('Table'[Amount])

for average 

Average+Per_employee = SUMX(values('Table'[Employee Number]),[Sum_Salary])/DISTINCTCOUNT('Table'[Employee Number])

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly