topic Re: avarage in DAX Commands and Tips

avarage

Paulo123 — Tue, 01 Mar 2022 16:01:16 GMT

Hi, I want to calculate the average of the years considering the value of each month, with each month being the result of a sum, how can i do this in PB

01/02/2018	221
01/03/2018	893
01/04/2018	109
01/05/2018	515
01/06/2018	771
01/07/2018	440
01/08/2018	223
01/09/2018	442
01/10/2018	862
01/11/2018	638
01/12/2018	692
01/01/2019	255
01/02/2019	686
01/03/2019	637
01/04/2019	147
01/05/2019	481
01/06/2019	747
01/07/2019	881
01/08/2019	966
01/09/2019	339
01/10/2019	826
01/11/2019	156
01/12/2019	943
01/01/2020	354
01/02/2020	172
01/03/2020	611
01/04/2020	639
01/05/2020	319
01/06/2020	769
01/07/2020	824
01/08/2020	751
01/09/2020	691
01/10/2020	903
01/11/2020	229
01/12/2020	471
avarage	560,0857143

Re: avarage

amitchandak — Wed, 02 Mar 2022 03:01:02 GMT

Paulo123 , Assuming you want some for month and average post that.

Have month year and year in this table of date tbale

AverageX(values('Date'[Month Year]), calculate(Sum(Table[Value])) )

Sum till month level avg post that. Use year in visual

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: avarage

Paulo123 — Thu, 03 Mar 2022 15:04:04 GMT

hi,

thanks, I got the correct values for each year, but if I just want an average considering all the months of the selected years, how do I do that?

Re: avarage

v-janeyg-msft — Fri, 04 Mar 2022 07:22:02 GMT

Hi, Paulo123

In theory, the value of measure will change with the context. Can you show how you lay it out in the visual? What's the formula you used in the end?

Janey

Re: avarage

Paulo123 — Fri, 04 Mar 2022 10:26:53 GMT

Hi, first i make a sum:

-SOMA SAIDA = CALCULATE(SUM(EX[VALOR]),E_S[Column1]="S")

second i calculate the average:

- Med = AVERAGEX(VALUES(CALENDARIO[Date].[Mês]),[SOMA SAIDA]).

here we can see that the sum is working correctly:

changing the layout, i get the annual average correctly:

but i want to get an average considering all months of all selected years, as the result of this excel:

Re: avarage

tamerj1 — Fri, 04 Mar 2022 10:49:55 GMT

Hi Paulo123
You need to iterate over the Year-Month column not the Month column. Something like

- Med = AVERAGEX ( VALUES ( CALENDARIO[Year Month] ), [SOMA SAIDA] )

Re: avarage

v-janeyg-msft — Fri, 01 Apr 2022 09:55:40 GMT

Paulo123

Have you tried tamerj1's advice? If your problem has been solved, you can mark your answer as solution to close the thread. If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

Re: avarage

Paulo123 — Fri, 01 Apr 2022 16:15:19 GMT

I don't know if I applied the solution correctly, but I created another column in the calendar with month and year and this column was used in the calculation.