Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average by volume range

Hi Everyone,   I have a fact table with location Ids and turnaround times, and measures that calculates the average turnaround time as well as counts the work order quantity. I have another table t...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ，

    Please try to create the below 2 measures:

    TatAvgStdDevFiltererd = 
 var selectedValue = MIN('SelectedVolumn'[30DayVolume])
var ds  = [30DayStdev]
return
AVERAGEX(FILTER(all('StDevModel'),'StDevModel'[30DayVolume]>selectedValue&&'StDevModel'[30DayVolume]<selectedValue+ds),[AvgTat])
    WoQtyAvgStdDevFiltererd = 
 var selectedValue = MIN('SelectedVolumn'[30DayVolume])
var ds  = [30DayStdev]
return
AVERAGEX(FILTER(all('StDevModel'),'StDevModel'[30DayVolume]>selectedValue&&'StDevModel'[30DayVolume]<selectedValue+ds),[WoQty])

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

What's your expected result? For example: loation 10, what's the final returned result? Please check if the below measure is what you want?

Measure = 
var _from=CALCULATE(SUM(StDevModel[30DayVolume]),ALLSELECTED(StDevModel))-[30DayStdev]
var _to=CALCULATE(SUM(StDevModel[30DayVolume]),ALLSELECTED(StDevModel))+[30DayStdev]
var _localid= CALCULATETABLE(VALUES(DimLocations[LocationId]),ALL(DimLocations),FILTER(ALL(StDevModel),StDevModel[30DayVolume]>_from&&StDevModel[30DayVolume]<=_to))
return AVERAGEX(FILTER(ALL('StDevModel'),'StDevModel'[LocationId] in _localid),'StDevModel'[30DayVolume])

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

     

    Thanks so much for your reply!

     

    This measure works really well for me such that it modifies the average of from the StDevModel[30DayVolume], and gives the result i would expect, but i am looking for the same effect on the AvgTat, and the WoQty measure as well. Could you assist me with how to write some dax to modifiy thease measures in this way?

     

    I have modeled what i expect to happen in an excel worksheet you can see here, im just not quite sure how to translate that into dax.

     

    Excel worksheet: http://www.filedropper.com/stddevexcel

     

    Thank you again for your reply!