Average by volume range

[Anonymous]

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 that lists the 30-day volume of work orders for each location id, and a measure in that table that calculates the standard deviation between those.

I am looking for some dax (which i will likely put in a card) to filter the average of the measures, in the fact table (WoQty and AvgTat), between locations, if the location’s volume in the 30-day fact table is within plus or minus the standard deviation measure (ie: volume-26.53 < volume, and volume < volume +26.53) of whatever location is selected in the slicer. The goal is to give some direction to location managers given locations of similar volumes.

So, for say, location 10 (a volume of 70, would select other volumes between 96 and 44), so it would take the avg for each measure, per location, for the following locations:

I have also uploaded my file with the model here so you could see what I’m working with, here.

LocationId	30DayVolume
10	70
13	60
14	88
23	45
32	62
33	46
34	48
39	82
57	64
62	48
79	64
81	56
84	87
600	56
608	86
610	58
613	61
617	72
620	50
621	66
622	72
624	50
625	51
627	67
634	49
639	61
651	92
658	48
659	60
663	48
669	44
672	47
703	56
704	82
705	67
741	56
746	70
901	75
902	48
904	72
907	57
912	85
914	89
925	51
927	85
928	75
929	84
930	54
935	76
936	78
937	60
938	79
940	95
942	90
945	83
946	69
947	85
948	50
949	52
951	92
952	71
953	65
954	67
955	90
956	81
959	55
960	80
961	71
965	74
969	62
970	56
971	61
972	93
974	93
975	70
976	77
977	83
978	55
979	83
980	77
982	86
984	83
985	54
986	95
988	48
989	63
995	83

[Anonymous]

Hi there,

Would anyone have come up to a solution for this?

Thanks,

[v-yiruan-msft · Accepted solution]

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

[v-yiruan-msft]

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]

Hi @v-yiruan-msft,

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!