Forum Discussion

jeremy-b's avatar
jeremy-b
Regular Visitor
4 years ago

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...
Need Help
jeremy-b's avatar
jeremy-b
Regular Visitor
4 years ago

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's avatar
amitchandak
Super User
4 years ago

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's avatar
    jeremy-b
    Regular Visitor
    4 years ago

    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.