topic Re: Average monthly salary by employee in DAX Commands and Tips

Average monthly salary by employee

pedroccamaraDBI — Mon, 26 Dec 2022 13:27:20 GMT

Hello everyone

As you all know, the salary is a set of codes for each month and person, right? Like the salary, food allowance, productivity allowance, etc.
I would like to know the average of the salary only for each person, each month, of a particular year. And this measeure would work if every month had values, which it does not.

Avg salary By Year & Employee =

AVERAGEX(

VALUES( 'Dates'[MonthOfYear]),

AVERAGEX(

VALUES( 'Employee'[WorkerName] ),

[Salary])) (this measure is a simple one that calculates a sum of a value filtered by the code of the salary name)

As an example, this worker have worked for 8 months in 2021 but only 3 months had values in the salary item. The other 5 months had values in other items diffferent than salary.

It's showing me the average of 540 but it should be 1440.
Could you please help me?
Thanks in advance

Re: Average monthly salary by employee

amitchandak — Mon, 26 Dec 2022 13:43:03 GMT

pedroccamaraDBI , Try like

Avg salary By Year & Employee =
var _calc = AVERAGEX(
VALUES( 'Employee'[WorkerName] ),
[Salary])
return
AVERAGEX(filter(
VALUES( 'Dates'[MonthOfYear]) , _calc <> 0 ),_calc)

Re: Average monthly salary by employee

pedroccamaraDBI — Mon, 26 Dec 2022 14:37:28 GMT

Hello amitchandak
Thank you for your answer.
I think you've manage to filter the non salary values,

But instead of the average it's showing the total.
I thought it would be enough to replace the VALUES function but not.
Can you still help me?

Re: Average monthly salary by employee

Anonymous — Tue, 27 Dec 2022 01:41:48 GMT

Hi pedroccamaraDBI ,

Please have a try.

Create a measure.

measure== var _a = 'table'[avg] var _b = SUMMARIZE('table','table'[date],"aaa",'table'[avg]) return IF(HASONEVALUE('table'[date]),_a,averagex(_b,[aaa]))

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Re: Average monthly salary by employee

pedroccamaraDBI — Tue, 27 Dec 2022 19:39:57 GMT

Hello Anonymous !!
I'm sorry but I don't understand this. Could you explain?

var _a = 'table'[avg]

Re: Average monthly salary by employee

Anonymous — Wed, 28 Dec 2022 01:25:20 GMT

Hi pedroccamaraDBI ，

var _a = 'table'[avg]

This is the measure you created before.

I have created a simple sample , please refer to it to see if it helps you.

Best Regards
Community Support Team _ Polly

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