to find the Standard_Deviation in window sum in power BI

[asdf1608]

I'm trying to convert Tableau function to power BI

There is this calculated field called Window_stdev() which returns the sample standard deviation of the expression within the window. The window is defined by means of offsets from the current row.

I got this standard deviation in power bi. 

STDEV.S() but it just returns sample standard deviation for values in a column.

I need for the window not the column.

How do I achieve this in power BI?

I have attached a sample PBI file

samplea.pbix

Help in this is really needed.

Thanks in advance.

[123abc]

1. Create a new measure in your data model by going to the "Modeling" tab and selecting "New Measure."

2. Use the following DAX formula to calculate the sample standard deviation within a window for your specific expression:

Window_stdev =
VAR CurrentRowValue = [YourExpressionColumn] -- Replace [YourExpressionColumn] with your actual expression column name
VAR WindowSize = 3 -- Change this value to specify the window size
VAR WindowStart = MAX(YourTable[Index]) - WindowSize + 1
VAR WindowEnd = MAX(YourTable[Index])
VAR ValuesInWindow =
FILTER(
ALL(YourTable),
YourTable[Index] >= WindowStart && YourTable[Index] <= WindowEnd
)
VAR SumSquaredDifferences =
SUMX(ValuesInWindow, (YourTable[YourExpressionColumn] - CurrentRowValue)^2)
RETURN
SQRT(SumSquaredDifferences / (WindowSize - 1))

In the above formula:

• YourExpressionColumn should be replaced with the column or expression you want to calculate the standard deviation for.
• YourTable should be replaced with the name of your table.
• Window_Size is the size of the window, i.e., how many rows you want to consider in your window.

1. Replace the variables and the measure name as needed for your specific data model.

2. Once you have created the measure, you can use it in your visuals to display the sample standard deviation within the defined window.

This measure will calculate the sample standard deviation for the values within the specified window size around the current row based on the column or expression you provide. Adjust the WindowSize variable to change the size of the window as needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[asdf1608]

@123abc 

If you could, please help me in sharing the formula in the attached PBI file. I'm little bit confused with the windowSize and Index.

I have attached the sample PBI help. Can you apply this formula in that file and send me.

Help in this is really needed.

Thanks in advance

[123abc]

I apologize for any confusion. Unfortunately, I cannot directly access or modify the content of attached files, including Power BI files. However, I can guide you on how to implement the formula in your Power BI file.

To implement the formula in your sample Power BI file, you can follow these steps:

1. Open your sample Power BI file (samplea.pbix).

2. In Power BI, you typically need a unique identifier for each row to establish the window and calculate the standard deviation within that window. You can create a custom column for the row index. To do this, follow these steps:

   a. In the Power Query Editor, select the table for which you want to calculate the standard deviation. b. Click on the "Add Column" tab. c. Choose "Index Column" and select "From 1."

3. Back in your Power BI report, create a new measure as described in my previous response. You can add this measure to the table or visualization where you want to display the standard deviation within the window.

   Here's the DAX formula again for your reference:

Window_STDEV =
VAR CurrentRowValue = [YourExpression]
VAR WindowStart = MAX(YourTable[RowNumber]) - 2 // Define the window size here (e.g., 3 rows)
VAR WindowEnd = MAX(YourTable[RowNumber])
VAR WindowValues = FILTER(YourTable, YourTable[RowNumber] >= WindowStart && YourTable[RowNumber] <= WindowEnd)
RETURN
STDEV.S(WindowValues[YourColumn])

Replace [YourExpression], YourTable, YourColumn, and YourTable[RowNumber] with your actual values.

1. Adjust the WindowStart and WindowEnd variables based on your desired window size.

2. Once you've created the measure, use it in your visuals to display the standard deviation within the defined window.

Please note that the formula assumes you have added a column called RowNumber using the "Index Column" step in the Power Query Editor, and this column represents the unique row identifier. The WindowStart and WindowEnd values will be relative to this row number.

Once you've added the measure, you can apply it to your visuals in your Power BI report to see the standard deviation within the specified window.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[asdf1608]

@123abc 

Sorry for disturbing.

I am getting a error at this STDEV.S(WindowValues[YourColumn])
The error is
Parameter is not correct type

The calculated field I recreated is

Window_STDEV =

VAR CurrentRowValue = SUM(SampleExcel[Quantity])

VAR WindowStart = MAX(SampleExcel[Index]) - 2 // Define the window size here (e.g., 3 rows)

VAR WindowEnd = MAX(SampleExcel[Index])

VAR WindowValues = FILTER(SampleExcel, (SampleExcel[Index]>= WindowStart && SampleExcel[Index] <= WindowEnd))

RETURN

STDEV.S(WindowValues[Qty LB])

I am not sure of what I'm missing.

I'm not sure of what yourExpression and yourColumn means. I need the window_stdev for a particular column Quantity So Should I apply the column in [yourexpression] and [yourcolumn]

This is the power BI file in which I have created. Please check if this is accessable
samplea.pbix

This is the excel connecting to PBI
Sample.XLSX

Help in this is really needed.
Thanks in advance

[asdf1608]

@123abc 

I request you to please help me in this.

Still I'm getting an error at the mentioned line 

STDEV.S(WindowValues[YourColumn])
The error is
Parameter is not correct type

I am not sure of what went wrong.
Can you please help me with this

Thanks in advance

[asdf1608]

@123abc 

Please help me in this. I am not getting the result I need.