Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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
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?
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
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)
Yes I want to group the table with a new column for calcuating standard deviation.
Dear Stachu / Tom please help me.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |