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.
