cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calc Std Dev of a ratio.

Hi all, I'm looking to add dynamic upper and lower controls to my line chart. I'm having issues getting the standard deviation however and am wondering if its the type of data I'm using. I'm using data from 2 date columns based on work orders, one column for when the work order was raised and one for when it was completed (it's blank if it is still open). The counts have to use inactive relationships as well so they are included in the formulas:

 

Raised_Count = CALCULATE(count(WorkOrders[Raised]), USERELATIONSHIP(WorkOrders[Raised],'Date'[Date]))  

Completed_Count = CALCULATE(count(WorkOrders[Completed]), USERELATIONSHIP(WorkOrders[Completed],'Date'[Date]))  

 

I then take these and divide the completed count by the raised count to get the ratio between the two:

Completed as % of Raised = DIVIDE([Completed_Count],[Raised_Count])  

I use 

Clearance Ratio = AVERAGEX( VALUES( 'Date'[Date (bins(Months))] ) , [Completed as % of Raised])

to get the values in a table and then use 

Clearance Ratio - Average = CALCULATE(AVERAGEX( VALUES( 'Date'[Date (bins(Months))] ) , [Completed as % of Raised]),ALLSELECTED('Date'[Date (bins(Months))]))

to get the 'flat' average over the time periods I specify in filters and slicers.    

 

I don't know how to calculate the standard deviation to allow me to build the upper and lower control limits. 

2 REPLIES 2
Highlighted
Super User IV
Super User IV
Highlighted
Community Support
Community Support

Hi @Thegeeza ,

 

Could you provide some sample data and the expected result for us to test? 

 

Reference: How to provide sample data in the Power BI Forum

 

 

Best Regards,

Icey

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors