Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 -
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |