Average and standard deviation

[jeremy-b]

Hi everyone,

I would like to calculate a 2-year average and standard deviation with the following information:

Division	Notification	Created on
A	00458287	02/01/2020
B	00458288	02/01/2020
A	00458289	03/01/2020
B	00458290	03/01/2020
B	00458291	03/02/2020
A	00458292	03/02/2020
B	00458293	03/03/2020
C	00458294	03/05/2020
B	00458295	04/05/2020
C	00458296	05/05/2020
C	00458297	06/06/2020
A	00458298	06/06/2020
A	00458299	06/06/2020
C	00458300	06/07/2020
C	00458301	07/07/2021
C	00458302	31/01/2021
C	00458303	31/01/2021
C	00458304	31/01/2021
B	00458305	02/02/2021
C	00458306	02/02/2021
B	00458307	02/02/2021
A	00458308	02/02/2021
B	00458309	16/03/2021
A	00458310	16/03/2021
A	00458311	16/03/2021
C	00458312	16/03/2021
A	00458313	16/03/2021
B	00458314	08/04/2021
A	00458315	09/04/2021
C	00458316	09/04/2021
B	00458317	09/04/2021

With a dynamic pivot table I can do it but I want to do it in Power BI.

thank you for your help.

[amitchandak]

@jeremy-b , If you need each row consider 12 months you can have measure like

Rolling 12 = CALCULATE(Average(Table[valuet]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

I do not see any value column, avg of ?

[jeremy-b]

Hi, @amitchandak 

thank you for your help.

to answer your question, I don't have any values because I need to first count the number of "notifications" per month and then calculate the average of this number.

I need to calculate the average per line (e.g: 2020 and 2021)

[amitchandak]

@jeremy-b , For Avg of Count/Sum

A measure like

averageX(Values('Date'[Month Year]), calculate(count(Table[Value])) )

This will work for Sub total and GT

Each row year wise Avg

averageX(Values('Date'[Month Year]), calculate(count(Table[Value])) )

This Year = CALCULATE(averageX(Values('Date'[Month Year]), calculate(count(Table[Value])) ),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

Last Year = CALCULATE(averageX(Values('Date'[Month Year]), calculate(count(Table[Value])) ),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]) -1 ))

[jeremy-b]

@amitchandak hi,

for my understanding, to use these measures I need to create a date table ?

There are errors in the average calculations, because there may be a month with no value.
For example, I have 11 notifications (January to November) for one year, but there are no notifications in December.

The average is calculated on the 11 values whereas there are 12 months. The last value in December should be counted as 0.