Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
taylorirwin21
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])
 
taylorirwin21_0-1694728075351.png

 

How can I work around this?

@Ashish_Mathur 

 

Much appreciated,

Taylor

9 REPLIES 9
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1695102436415.png

 

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. 

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? 

taylorirwin21_0-1695215668922.png

 

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. 

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
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Ashish_Mathur_0-1695440933886.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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.

taylorirwin21_0-1694785453662.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.