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

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

4 Replies

  • 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi there,

     

    Would anyone have come up to a solution for this?

     

    Thanks,

    • Anonymous's avatar
      Anonymous
      Not applicable
      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