topic Window_stdev table(across) along with date in power BI in DAX Commands and Tips

Window_stdev table(across) along with date in power BI

asdf1608 — Thu, 23 Nov 2023 12:56:50 GMT

I am trying to convert tableau dashboard to poewr BI. I am facing an isssue in converting a calculated column called window_stdev.

The formula in tableau is :

WINDOW_STDEV(SUM([Quantity 2])).

It seems to be calculating table across specific dimension by date

The result from tableau is

The formula I used in power BI :

Rolling Stdev =

VAR CurrentRowDate = aa[Date]

VAR RowsToInclude =

FILTER(

ALL(aa),

aa[Date] >= (CurrentRowDate - 2) && aa[Date] <= CurrentRowDate

)

RETURN

IF(

COUNTROWS(RowsToInclude) > 1,

STDEVX.P(RowsToInclude, aa[Quantity2]),

BLANK()

) // this is not giving me correct answer.

The above formula is not giving me the answer same as tableau

The result from power BI is

I am attaching the excel file and poewr BI file

sample.XLSX

Sample.pbix

Help in this is really needed.

Thanks in advance

Re: Window_stdev table(across) along with date in power BI

lbendlin — Sat, 25 Nov 2023 03:22:34 GMT

Your sample table "aa" has 213K rows, but only 18K distinct rows. What are all these duplicates?

Are you sure you want to calculate the standard deviation across three values only? That will be statistically useless.

Re: Window_stdev table(across) along with date in power BI

asdf1608 — Mon, 27 Nov 2023 06:59:05 GMT

lbendlin

certain id may repeat. I need the standard deviations for the window across date.

normal standard deviation is coming in power BI. But for the windows it is giving me an wrong answer

Re: Window_stdev table(across) along with date in power BI

lbendlin — Mon, 27 Nov 2023 16:29:03 GMT

Where is this filter

FILTER(

ALL(aa),

aa[Date] >= (CurrentRowDate - 2) && aa[Date] <= CurrentRowDate

)

in Tableau? From your example WINDOW_STDEV calculates across all dates for each ID.

Re: Window_stdev table(across) along with date in power BI

asdf1608 — Tue, 28 Nov 2023 07:32:03 GMT

lbendlin There are no filters. As you said WINDOW_STDEV calculates across all dates for each ID.

I just used the above DAX. It might be wrong.

That's why I raised this.
I am not sure why to create DAx so that the values matches with Tableau

Re: Window_stdev table(across) along with date in power BI

lbendlin — Tue, 28 Nov 2023 14:37:41 GMT

How do you know the Tableau values are correct?

Re: Window_stdev table(across) along with date in power BI

asdf1608 — Wed, 13 Dec 2023 10:06:19 GMT

lbendlin

I am not sure if the tableau result is correct. All I want is the formula conversion.

Window_stdev to be calculated across the specified dimension

WINDOW_STDEV(SUM([Quantity 2])).

It seems to be calculating table across specific dimension by date

Please help me in this

Thanks in advance

Re: Window_stdev table(across) along with date in power BI

asdf1608 — Wed, 13 Dec 2023 10:06:24 GMT

lbendlin

I am not sure if the tableau result is correct. All I want is the formula conversion.

Window_stdev to be calculated across the specified dimension

WINDOW_STDEV(SUM([Quantity 2])).

It seems to be calculating table across specific dimension by date

Please help me in this

Thanks in advance