Standard Deviation for a Calculated Measure

[taylorirwin21]

Hi,

I am trying to calculate the standard deviation of a Measure in Power BI. The measure calculates the LN return of price changes. When I try to use the function for stadnard deviation below, I only get zeros. 

LN Return = LN([Price Measure]/[Previous_Price]) 

LN Return StDev = STDEVX.P(Prices,[LN Return])

How can I work around this?

@Ashish_Mathur 

Much appreciated,

Taylor

[v-tangjie-msft]

Hi @taylorirwin21 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

LN Return StDev = STDEVX.P(TOPN(10,FILTER(ALL('Table'),'Table'[date]<=MAX('Table'[date])),[date],DESC),'Table'[_LN Return])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[taylorirwin21]

Hi Neeko,

Thank you for the reply - my report is still showing zeros for standard deviation when I try your solution. Could it be because my 'Previous Price' and 'LN Return' are both measures themselves? If so, is there a way to convert them to calculated columns or some other fix? 

[v-tangjie-msft]

Hi @taylorirwin21 ,

Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out. Thanks in advance.

Best Regards,

Neeko Tang

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

[v-tangjie-msft]

Hi @taylorirwin21 ,

As my pbix shows, the 'Table'[_LN Return] in the LN Return StDev measure is also the measure.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

[Ashish_Mathur]

Share the file when you can from a non work laptop.

[taylorirwin]

https://1drv.ms/x/s!AuMjQWEKLS_BxRcl0Xecn87kttbO?e=d4PJaT 

[Ashish_Mathur]

Hi,

Please find attached my solution file.  My answers do not match with your but my workings are correct.  Surprisingly, for 13/9/2023, the LN function in MS Excel reveals the answer as 0.00162, the same function returns the result as 0.00216 in PowerBI.

Hope this helps.

[pallavi_r]

Thanks @ lot @Ashish_Mathur , this helped a lot in resolving standard deviation issue.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Create the expected result table in an MS Excel file (with the formulas already there) and share the download link of that file.

[taylorirwin21]

Unable to share a link as it's a work computer, but here is what it should look like! Should be a rolling StDev. Just used the Stdev formula in excel here.