Latest Enterprise DNA Initiatives

Dax measure for average salary

Hello all
I have this table attached, Tab MovFuncionarios, which has codnumber, codmov, datamov and value. This table has an inactive relation with dates (Tab Dates).
If i want to have the total for remunerations, i did this first:

T Value = CALCULATE(SUM( ‘Tab MovFuncionarios’[Value]),
USERELATIONSHIP( ‘Tab Dates’[Date], ‘Tab MovFuncionarios’[DataMov]))

Then this,

Remunerations = CALCULATE( [T Value], FILTER( ‘Tab CodMov’, ‘Tab CodMov’[Categoria] = “Remunerações” ))
Of course i have another measures from this T Value measure and they’re working fine. All i want now is to now the average of the remunerations.
My report has a date and a cost center filter. My visual is by name and month-year.

I would like to see the average for each person and month-year and in another visual the average for the year and the average for each cost center.

Can you help?
Thanks a lot
Pedro
Average.xlsx (121.7 KB)

Hi Pedro,

AVERAGEX in combination with VALUES should help you out!

Suppose you have 5 countries where you sell products and you want to show average sales across those countries over time:

1/1/2021 100$
1/2/2021 58$
etc…

Then the measure would be: Average sales per country = AVERAGEX(
VALUES(Country[ID],
[Sales])
( Here Sales is a simple measure with the sum of all sales)

Basically this table loops over each row in the datum output and calculates the average sales over all countries.

Hey @Rens
Maybe i haven’t explain well above. I want the average of remunerations, and later for discounts, and also social security, etc. But right now, remunerations. And Remunerations is a calculation of T Value filter by codmov = R1
I hope i was now clear
Thanks a lot

Hi Pedro,

it would help if you could share the Power BI file (or a copy with anonymized data) as the requirement is not yet 100% clear for me!
To be more precise, it is not clear why you can not use an Average(remunerations) function. It would really help if a PBI file would accompany your story!

Kind regards,
Rens

Hi @Rens
Here it goes.
I hope your connection is better than mine cose this takes forever to open and to work with it and such a small file…
Thanks a lot
Pedro

Hi @pedroccamara,

hope this helps:
AVG Remunerations =
AVERAGEX(
VALUES( ‘Tab CodMov’[Cod] ),
[Remunerações] ).

Kind regards,
Matteo

1 Like

Thank you Pedro.

I think using the VALUES formula to calculate the average value should work. Pedro also provides a concrete example (instead of the pseudo code I provided above) which works in your PBIX file.

What does the formula do? It checks for every date (if you add the measure to the data table for example) what the average remuneration across each Cod is.

1 Like

Guys,
Thanks a lot for your answers, but although both came up with the same solution, it’s not working.
Capture
Above is total remunerations and beside is the AVG Remunerations.
Is it possible to be missing in the measure something about dates?
Thanks a lot guys!!

P.S. Just a reminder: the measure Remunerações is like this:

Remunerações =
CALCULATE (
[T Value],
FILTER ( ‘Tab CodMov’, ‘Tab CodMov’[Categoria] = “Remunerações” )
)

and T Value is :

T Value =
CALCULATE (
SUM ( ‘Tab MovFuncionarios’[Value] ),
USERELATIONSHIP ( ‘Tab Dates’[Date], ‘Tab MovFuncionarios’[DataMov] )
)

What is in the row of your screenshots?

The name of the employee and month year

your question put me thinking…and came up with this solution:

AVG Remunerations =
AVERAGEX (
VALUES ( ‘Tab Dates’[Month & Year] ),
[Remunerações] )

Capture

1 Like

Good insight, Pedro! Hope you find the correct implementation of the formula in your datamodel.

1 Like

yes, me too. I’ll get back to you later (sometimes we just need a push in the right direction)

Great Pedro! Hope it will work

Hey guys,

I finally found this solution and it works for me, thanks to Sam’s videos

AVG Remunerations monthly =
VAR AVGRem = AVERAGEX(VALUES( ‘Tab Dates’[MonthOfYear]),[Remunerações])

RETURN
IF( HASONEVALUE( ‘Tab Dates’[MonthOfYear] ),
AVGRem,
AVERAGEX( VALUES( ‘Tab Dates’[MonthOfYear] ), AVGRem ))

With this i will have the monthly average that i needed.
Thanks a lot
Pedro

1 Like