Average monthly salary by employee

[pedroccamaraDBI]

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

[amitchandak]

@pedroccamaraDBI , Try like

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

[pedroccamaraDBI]

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?

[v-rongtiep-msft]

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.

[pedroccamaraDBI]

Hello @v-rongtiep-msft  !!
I'm sorry but I don't understand this. Could you explain?

var _a = 'table'[avg]

[v-rongtiep-msft · Accepted solution]

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.