Need help in calculating standard deviation using custom column

[Anonymous]

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.

[v-yulgu-msft · Accepted solution]

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

[Stachu]

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?

[TomMartens]

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-dax

Hopefully this gets you started

Regards

Tom

[Anonymous]

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.

[Stachu]

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)

[Anonymous]

Yes I want to group the table with a new column for calcuating standard deviation.

Dear Stachu / Tom please help me.