cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Standard Deviation for a Calculated Measure

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?

Much appreciated,

Taylor

9 REPLIES 9
Community Support

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.

Regular Visitor

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?

Community Support

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.

Community Support

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors