How to Calculate Std. Dev. of Measure after Aggregating Rows with DAX/PowerBI

Question

I have a table like this with a calculated step rate column. I would like to get a summarized step rate for certain hours going back 5 weeks.

Here's the summarized step rates.

Then I would like to get the std. dev. of those summarized step rates.

How can I create a measure here that takes the standard deviation of these five values? When I try to solve the problem I end up simply adding Step Rates in the aggregate table which gives me a huge value that is wrong.

This is the DAX I'm using right now that gets high values: Please let me know if there is an easier approach here.

Daily Base Fare StdDev N Weeks = 

var _curr = max('???? Booking'[Hour Offset]) //current selection

var _currDOW = max('???? Booking'[Day of Week]) //selection dow

var _currHour = max('???? Booking'[Hour]) //selection hour

var _N = 5 //weeks to go back

var _offset = _curr -24*7*(_N) //oldest datehour in range

var _steprate = sum(hourlyfunnelsteps[stepRate])


var x_sum = calculate(_steprate,

filter(all('???? Booking'),

'???? Booking'[Hour Offset] >= _offset &&

'???? Booking'[Hour Offset] < _curr &&

'???? Booking'[Day of Week] = _currDOW &&

'???? Booking'[Hour] = _currHour

))


var mean = divide(x_sum,_N)


var dev_sq_sum = sumx(

filter(all('???? Booking'),

'???? Booking'[Hour Offset] >= _offset &&

'???? Booking'[Hour Offset] < _curr &&

'???? Booking'[Day of Week] = _currDOW &&

'???? Booking'[Hour] = _currHour),

power(_steprate - mean,2))


var result = sqrt(dev_sq_sum / _N)

return
x_sum

What is the column are you summarizing on and what aggregation are you doing to get your summarized table? — Alexis Olson
That left column is hour offset from maximum hour. And the step rate = sum(nextStep column) / sum(currentStep column) — Andrew Marotta

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-09T15:52:46

You measure certainly won't work as expected because you're trying to modify a constant using CALCULATE.

[...]
var _steprate = sum(hourlyfunnelsteps[stepRate])

var x_sum = calculate(_steprate,
[...]

With regard to your larger question though, I think you could write something along these lines:

StdDevCalc =
VAR < ...selections... >
VAR Summary =
    SUMMARIZE (
        FILTER ( ALL ( '📅 Booking' ), < ...filter conditions... > ),
        '📅 Booking'[Hour Offset],
        "StepRate", [SumNextStep] / [SumCurrentStep]
    )
RETURN
    STDEVX.P ( Summary, [StepRate] )

The built-in STDEVX.P function can operate on any table expression, including ones created and stored as a variable.

How to Calculate Std. Dev. of Measure after Aggregating Rows with DAX/PowerBI

1 Answers