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)
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
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!
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
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.
Guys,
Thanks a lot for your answers, but although both came up with the same solution, it’s not working.
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:
