topic Standard Deviation in Power BI for the window in DAX Commands and Tips

Standard Deviation in Power BI for the window

asdf1608 — Wed, 31 Jan 2024 10:24:09 GMT

I am trying to do a standard deviation for the window size in Power BI - but it doesn't work like it does in Tableau and I cant figure out what I am doing wrong.

Calculations I am using in Tableau Listed Below

STD Dev : WINDOW_STDEV(SUM([Qty LB]))

Here the standard deviation is being calculated for the column Qty LB across the month Pstnd Date for the three rows.

I figured the concept behind this might becoming in that range
Window:

2022: [-2,461,606.68]

2023: [-2,461,606.68, -1,700,665.39, 7,300]

Mean (Average):

2022: -2,461,606.68

2023: (-2,461,606.68 - 1,700,665.39 + 7,300) / 3 ≈ -718,657.34

Squared Differences:

2022: [(-2,461,606.68 - (-2,461,606.68))^2] = [0]

2023: [(-2,461,606.68 - (-718,657.34))^2, (-1,700,665.39 - (-718,657.34))^2, (7,300 - (-718,657.34))^2]

Variance:

2022: Variance = Sum of squared differences / (number of elements - 1) = 0 / (1 - 1) = undefined (as there is only one element) 2023: Variance = Sum / 2 Standard Deviation: 2023: Square root of Variance ≈ sqrt(Sum / 2) ≈ 242,116.71 Therefore, the result for window_stdev(measure values, -2, 0) for the row with Material '10403729' for the year 2023 is approximately 242k.

This is for year(Pstng date)

For Month it is getting differed

Power BI Formulas I have.

Windows Stdev =

VAR CurrentMaterial = MAX('Sample'[Material])

VAR CurrentDate = MAX('Sample'[Pstng Date])

VAR WindowStart = CALCULATE(MAX('Sample'[Pstng Date]), FILTER('Sample', 'Sample'[Material] = CurrentMaterial && 'Sample'[Pstng Date] <= CurrentDate))

VAR WindowEnd = WindowStart + 7

VAR WindowValues = CALCULATETABLE('Sample', 'Sample'[Pstng Date] >= WindowStart && 'Sample'[Pstng Date] <= WindowEnd && 'Sample'[Material] = CurrentMaterial)

VAR CountRowsWindow = COUNTROWS(WindowValues)

VAR WindowMean = AVERAGEX(WindowValues, 'Sample'[Qty LB])

VAR SquaredDifferences = SUMX(WindowValues, ('Sample'[Qty LB] - WindowMean)^2)

VAR Variance1 = DIVIDE(SquaredDifferences, CountRowsWindow)

RETURN IF(CountRowsWindow > 0, SQRT(Variance1), BLANK())

can anyone state me the reason why the the formula is not working and How do i get this in Power BI

Help in this really needed.

I have attached the PBI File

sAMPLE.pbix

Thanks in advance

Re: Standard Deviation in Power BI for the window

lbendlin — Tue, 30 Jan 2024 21:58:59 GMT

You may want to use the built-in statistics functions for that rather than roll your own.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Standard Deviation in Power BI for the window

asdf1608 — Wed, 31 Jan 2024 10:26:16 GMT

lbendlin

I have attached the Power BI file for reference

sAMPLE.pbix

The window_stdev should be calculated for the column "qty lb" across the date column "Pstng Date"

Re: Standard Deviation in Power BI for the window

lbendlin — Thu, 01 Feb 2024 00:52:29 GMT

like this ?

What window size are we talking here? months in years?

Re: Standard Deviation in Power BI for the window

asdf1608 — Thu, 01 Feb 2024 07:46:52 GMT

lbendlin

The standard deviation needs to be calculated for the entire window size. For example here the window_size is 3 so first the standard deviation is calculated based on the three values of Qty LB for the material

Material Qty LB

10403709 -2,461,606.68,

10402077 -1,700,665.39,

10402008 7,300

The window_stdev is standard deviation first calculated for the three material values (-2,461,606.68, -1,700,665.39, 7,300) then two then NULL

This is a direct formula in tableau Window_stdev(sum(qty LB))

My question is how to convert this to power BI DAX

there are no direct formula avaialable. So how to do this?

Re: Standard Deviation in Power BI for the window

lbendlin — Thu, 01 Feb 2024 22:27:09 GMT

I would think you have to use SUMMARIZE first to freeze the window and then run STDEV over the resulting table. The tricky part will be to figure out what to SUMMARIZE over.

Re: Standard Deviation in Power BI for the window

asdf1608 — Fri, 02 Feb 2024 07:38:56 GMT

lbendlin

Summarize should be based on material.

CAn you send me a DAX using summarize to achieve windows_stdev?

i haven't got the result for this.

This is the DAX I used

Windowed_Stdev =

VAR WindowSize = 3

VAR SummaryTable =

SUMMARIZE(

'Sample',

'Sample'[Material],

'Sample'[Pstng Date],

"TotalQtyLB", SUM('Sample'[Qty LB])

)

VAR CurrentMaterial = MAX('Sample'[Material])

VAR CurrentDate = MAX('Sample'[Pstng Date])

VAR StartDate = CurrentDate - (WindowSize - 1)

VAR EndDate = CurrentDate

RETURN

CALCULATE(

STDEV.P(('Sample'[Qty LB])), -- Corrected reference to the summarized column

FILTER(

ALL('Sample'),

'Sample'[Material] = CurrentMaterial &&

'Sample'[Pstng Date] >= StartDate &&

'Sample'[Pstng Date] <= EndDate

)

But I'm getting the result same as ordinary Standard_devviation.
I need for Winsow_stdev

Help in this is really needed

Thanks in advance