Forum Discussion
Need help in calculating standard deviation using custom column
Dear All,
I would like to calcuate standard devation on below data using Custom column, please help I'm new to Power BI tool. Data looks like below
Source Data:
| Site | Code | DAY_DATE | DAYS |
| YP | YPSL152 | 04Jul2015 0:00:00 | 0 |
| YP | YPSL152 | 12Jul2015 0:00:00 | -8 |
| YP | YPSL152 | 10Aug2015 0:00:00 | -29 |
| YP | YPSL152 | 12Dec2015 0:00:00 | -124 |
| YP | YPSL152 | 14Dec2015 0:00:00 | -2 |
| YP | YPSL152 | 17Jun2016 0:00:00 | -186 |
| YP | YPSL152 | 19Dec2016 0:00:00 | -185 |
| YP | YPSL152 | 22Dec2016 0:00:00 | -3 |
| YP | YPSL152 | 21Feb2017 0:00:00 | -61 |
| CG | CG155 | 11May2015 0:00:00 | 0 |
| CG | CG155 | 11May2015 0:00:00 | 0 |
| KB | KB155 | 18Dec2015 0:00:00 | 0 |
| KB | KB155 | 14Feb2016 0:00:00 | 58 |
| KB | KB155 | 23Dec2016 0:00:00 | 313 |
| KB | KB155 | 20Dec2017 0:00:00 | 362 |
| KB | KB155 | 18Dec2015 0:00:00 | 0 |
| KB | KB155 | 14Feb2016 0:00:00 | -58 |
| KB | KB155 | 23Dec2016 0:00:00 | -313 |
| KB | KB155 | 20Dec2017 0:00:00 | -362 |
| FE | 23Nov2017 0:00:00 | 0 | |
| FE | 27Nov2017 0:00:00 | 4 | |
| FE | 26Apr2018 0:00:00 | 150 | |
| FE | 23Nov2017 0:00:00 | 0 | |
| FE | 27Nov2017 0:00:00 | -4 | |
| FE | 26Apr2018 0:00:00 | -150 |
Expected Output:
| Site | Code | STD_OF_DAYS |
| YP | YPSL152 | 102.231571 |
| CG | CG155 | 0 |
| KB | KB155 | 257.6692232 |
| FE | 94.90205477 |
I have tried using Transform --> Statitistcs --> Standard Devatation which didn't worked for me. please guide mw how can i get expected out put as parked above.
In case of any more information required please do let me know, Thanks in advance for you help.
Hi Anonymous,
Please try below Power Query.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data (Autosaved) (Autosaved).xlsx"), null, true), data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site", type text}, {"Code", type text}, {"DAY_DATE", type datetime}, {"DAYS", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Site", "Code"}, {{"standard deviation", each List.StandardDeviation([DAYS]), type number}}) in #"Grouped Rows"Best regards,
Yuliana Gu
6 Replies
Hey,
using the DAX function STDEV.S by creating a measure like so:
Measure = CALCULATE( STDEV.S('Table2'[DAYS]) )creates this output:
Here you will find guidance how to create a measure:
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-measures
And here is something about the DAX function STDEV.S
https://msdn.microsoft.com/en-us/query-bi/dax/stdev-s-function-daxHopefully this gets you started
Regards
Tom
Thanks Tom & Stachu for your reply, I want same kind of logic to be done under query editor --> Custom Column because I need that result in a new column so that i can build rest of logic on top of it.
Stachu - It's actual standard devation data source & Expected output which is implemented in diff tool which we are migrating in power bi.
so you want to group the table with a new column calculating stadard deviation?
one more question - is the output you showed based on exacly same dataset that you posted? When I calculate the standard deviation I get the same results as the ones that TomMartens has posted, which are not consistent with your outcome (see YP site)
in PowerBI Modelling tab you can create this measure:
STD_OF_DAYS = STDEV.P('Source Data'[DAYS])
it gives values different than i your expected result- did you put dummy data there?Hi Anonymous,
Please try below Power Query.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data (Autosaved) (Autosaved).xlsx"), null, true), data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site", type text}, {"Code", type text}, {"DAY_DATE", type datetime}, {"DAYS", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Site", "Code"}, {{"standard deviation", each List.StandardDeviation([DAYS]), type number}}) in #"Grouped Rows"Best regards,
Yuliana Gu
