Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Weekly Average

Dear all,

 

I'm trying to obtain (either as a custom column or through a measure) the weekly average of the Import - Units (see below table, first column from the right). 

 

As you can see, there are multiple sizes/types of containers, multiple ports of origin and destination, which add complexity.

 

For example, taking into consideration the first row, I'd like to know the Weekly Average Import Units that are:

  • 20DV
  • with Port of Origin Code "BRNVT"
  • with Intended Port of Destination Code "ALDRZ"

I should then be able to obtain aggregated weekly averages at, for instance, Destination Country level or Destination Region level.

 

Container Size TypePort Of Origin CodePort Of Origin RegionDestination CountryPort Of Destination CodeDestinationRegionImport DateImport - Units
20DVBRNVTSAECALBANIAALDRZBALKAN05.01.2018 00:001
20DVCNDLCNORTH CHINAALBANIAALDRZBALKAN05.01.2018 00:002
20DVCNNGBNORTH CHINAALBANIAALDRZBALKAN05.01.2018 00:0028
20DVCNNSAS. CH + HK + TWALBANIAALDRZBALKAN05.01.2018 00:004
40DVCNQZHS. CH + HK + TWALBANIAALDRZBALKAN05.01.2018 00:005
40DVCNSHKS. CH + HK + TWALBANIAALDRZBALKAN05.01.2018 00:0061
40DVCNTAONORTH CHINAALBANIAALDRZBALKAN05.01.2018 00:004
40DVCNTXGNORTH CHINAALBANIAALDRZBALKAN05.01.2018 00:0019
40HCCNXMNS. CH + HK + TWALBANIAALDRZBALKAN05.01.2018 00:0020
20RECNYTNS. CH + HK + TWALBANIAALDRZBALKAN05.01.2018 00:006
20DVGBLGPNWC + UK + IRLANGOLAAOLADANG + NAM12.06.2018 00:001
20DVIDSUBSE ASIAANGOLAAOLADANG + NAM12.06.2018 00:0023
20DVINHZAN. INDIA + PAKANGOLAAOLADANG + NAM12.06.2018 00:0016
20DVITGOAWEST MEDANGOLAAOLADANG + NAM12.06.2018 00:002
20DVITNAPWEST MEDANGOLAAOLADANG + NAM12.06.2018 00:0014
20DVITSPEWEST MEDANGOLAAOLADANG + NAM12.06.2018 00:0016
20DVJPHIJJAPANANGOLAAOLADANG + NAM12.06.2018 00:001

 

Any assistance will be much appreciated.

 

 

 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous ,

     

    You can create column Average to get aggregated weekly averages at Destination Country level or Destination Region level.

     

    Average = CALCULATE(AVERAGE(Table1[Import - Units]),FILTER(ALLSELECTED(Table1),OR(Table1[Destination Country]=EARLIER(Table1[Destination Country]),Table1[DestinationRegion]=EARLIER(Table1[DestinationRegion]))))

     

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    You can create column Average to get aggregated weekly averages at Destination Country level or Destination Region level.

     

    Average = CALCULATE(AVERAGE(Table1[Import - Units]),FILTER(ALLSELECTED(Table1),OR(Table1[Destination Country]=EARLIER(Table1[Destination Country]),Table1[DestinationRegion]=EARLIER(Table1[DestinationRegion]))))

     

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.