Forum Discussion
Average and standard deviation
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.
4 Replies
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 ?
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)
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 ))
