Standard Deviation issue

[HarryS]

Hello,

Bit of a strange request. My organisation recently started using Power BI and we are transitioning from our old way of reporting to equivalent Power BI Dashboards and struggling a bit with standard deviation.

Basically, the way our data is set up is we count IDs for each date, which have a whole lot of corresponding info with them (such as which group they belong to, which geographic area, etc.). This gives us our totals when combined with a data table (i.e. X no. of IDs were inputed on X date, or during X week, or whatever). Previously, we would use these daily totals to work out the mean and standard deviation, but using the standard deviation DAX either returns an error or 0, presumably because it isn't being given anything numeric to actually count. Is there any way of rendering non-numeric data numeric (i.e. counting and grouping IDs by week) in a measure, and then performing the standard deviation function on that?

I'm sorry that I cannot provide actual data, as it is confidential, but to give you an idea, here is a very basic representation (in reality there are multiple columns):

ID                Date                
G4J1           01/01/19        
G4J2           01/01/19        
G4J3           02/01/19        
G4J4           02/01/19
G4J5           02/01/19
G4J6           03/01/19
G4J7           03/01/19
G4J8           04/01/19
G4J9           05/01/19
G4G1          05/01/19

Meaning that in this example:
01/01 = 2

02/01 = 3

03/01 = 2

04/01 = 1

05/01 = 2
The mean = 2, SD = 0.632455532.

Please let me know if I can be clearer, or if there is a different way to approach this than using the SD function.

Cheers!

[Anonymous · Accepted solution]

@HarryS -

You can do the following:

Std Dev = 
var a = SUMMARIZE(yourtable,yourtable[Date],"countitems", COUNT(yourtable[ID]))
return STDEVX.P(a,[countitems])

Mean = 
var rowcount = COUNTROWS(yourtable)
var datecount = DISTINCTCOUNT(yourtable[Date])
return DIVIDE(rowcount,datecount)

Cheers!

Nathan

[HarryS]

Hello Nathan,

Thank you very much for your help, that seems to work. I'll have a go trying a few different things with it and make sure it's fit for purpose. Thanks again.

Best wishes,

Harry