Calculate standard deviation from value and frequency columns in Power BI
I am trying to calculate the standard deviation of a set of values in PowerBI and I am stuck. The table has two columns (days and number). This is the frequency distribution of the transport band. Days go from 1 to 100, the number is the number of departures that took these days.
The formula for calculating the standard deviation of the frequency distribution is pretty straight forward: sqrt (sum (fx * (x - avgx) ^ 2)) / sum (fx)) But Dax is giving me a massive headache. Any help is appreciated. Thank.
source to share
I took an example from the Wikipedia Standard Deviation Page as sample data.
Converted to Power BI equivalent and meets your requirement as days and quantity:
And this measure is created as follows, the tricky part is using the SUMX function . I deliberately break down the intermediate steps with with VAR
to make it clearer.
st_dev =
VAR x_sum = SUMX(Lane, Lane[Days] * Lane[Count])
VAR x_count = SUM(Lane[Count])
VAR mean = x_sum / x_count
VAR dev_sq_sum = SUMX(Lane, POWER(Lane[Days] - mean, 2) * Lane[Count])
RETURN SQRT(dev_sq_sum / x_count)
Result:
PS Power BI actually has some built in functionality for calculating the standard deviation, for example. STDEVX.P , but it is not that useful in this case. Feel free to check it out though.
source to share