Forum Discussion

Helper III

9 years ago

Standard Diviation

I have a set of data for student's score for 2 years. i will get the standardard deviation of list of student on picked day. how to do that? Output will look like this. Name Score Standard...

Helper III

9 years ago

v-ljerr-msft have you tried to pick the specific date. for example every wednesday for the past 2 years? How to do that?

Microsoft Employee

9 years ago

Hi ask,

The formula below should work. :smileyhappy:

Score SD =
CALCULATE (
    STDEV.P ( data[Score] ),
    FILTER ( data, WEEKDAY ( data[Date] ) = 4 && YEAR ( data[Date] ) >= 2015 )
)

Regards

ask
Helper III
9 years ago
v-ljerr-msft How about to check the dynamic date instead of wednesday, that might be Tuesday, Monday, etc, and also dynamic name selected.

Score SD = CALCULATE ( STDEV.P ( data[Score] ), FILTER ( data, WEEKDAY ( data[Date] ) = 4 && YEAR ( data[Date] ) >= 2015 ) )
- v-ljerr-msft
  Microsoft Employee
  9 years ago
  Hi ask,
  
  1. Add [Year], [MonthName], [WeekName] column to your table if you have them yet.
  
  Year = YEAR(data[Date])
  
  MonthName = FORMAT(data[Date],"MMMM")
  
  WeekName = FORMAT(data[Date],"DDDD")
  
  2. Simply use the formula below to create a measure for 'Score SD', then you should be able to use the [Year], [MonthName], [WeekName], [Name] column as Slicers on the report to dynamically get the standard deviation of list of student on picked day.
  
  Score SD = CALCULATE ( STDEV.P ( data[Score] ) )
  
  Regards
  - ask
    Helper III
    9 years ago
    Name Score Standard deviation of Wellness Score Score STDEV.P
    NAME 1 768 0 8
    NAME 2 273 0 18
    NAME 3 190 0 38
    NAME 4 96 0 7
    NAME 5 94 0 14
    NAME 6 96 0 8
    NAME 7 98 0 1
    NAME 8 98 0 5
    NAME 9 96 0 2
    NAME 10 86 0 8
    
    Hi v-ljerr-msft the table doesn't have Year, Month, Week. Only have Name, Score, Date as shown above

Related content