CODEWITHSUNDEEP
powerbipowerquery
user21196480
user21196480

Reputation: 3

How to consider specific list of dates for calculating standard deviation in power bi

I have a table which contains three columns. Column 1 - Employees (A & B), Column 2 - List of dates (different for each employees), Column 3 - Change in price for each list of dates.

I have to calculate the standard deviation for each employee based on the change in price for the two different list of dates.

when i'm using standard deviation formula in power query or in power bi. The standard deviation is getting results for all the dates and not for the specific list of dates. i.e., if the total dates is from 1st January to 31st January and for employee A, the list of dates is from 1st to 10th and for employee B the list of dates is from 20th to 31st. the formula calculates the standard deviation for 1st to 31st and not for the specific dates for each employee.

Is it possible for me to do this in power query? Any help would be appreciated.

Upvotes: 0

Views: 531

Answers (2)

Peter
Peter

Reputation: 12375

In PowerQuery use:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxDoAgDIXhuzBrgi1FGPUahPtfwz4dBFsTl/cNpP6thSMsgSLxGjf97hH68nHSsTnOOtjxhCHWBe84nuHF+o5B1svPnVWHfV4v1zMfPwcnuETrQ4bJkaE4zm+eyZGhOo4M4tyDDMnx/c08OTIkx5EhW+efDoz/zer9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, #"Change in price" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Employee", type text}, 
            {"Date", type date}, 
            {"Change in price", Int64.Type}
        }
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", {"Employee"}, 
        {
            {"Stdev", each List.StandardDeviation([Change in price]), type nullable number}
        }
    )
in
    #"Grouped Rows"

Basically you start with the "Group By" GUI

enter image description here

and then go into the M-Code and replace List.Average with List.StandardDeviation, since this operation is not directly available in the GUI.

Upvotes: 0

Peter
Peter

Reputation: 12375

PowerQuery is made for ETL, DAX is made for Analysis.

If you need a result column use:

Stdev of change = STDEV.S('Table'[Change in price])

enter image description here

If you want to have single results:

Stdev A = 
CALCULATE(
    STDEV.S('Table'[Change in price]),
    'Table'[Employee] = "A"
)

Stdev B = 
CALCULATE(
    STDEV.S('Table'[Change in price]),
    'Table'[Employee] = "B"
)

enter image description here

Sample data:

enter image description here

Upvotes: 1

Related Questions