Calculate Weekly Average

[Anonymous]

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 Type	Port Of Origin Code	Port Of Origin Region	Destination Country	Port Of Destination Code	DestinationRegion	Import Date	Import - Units
20DV	BRNVT	SAEC	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	1
20DV	CNDLC	NORTH CHINA	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	2
20DV	CNNGB	NORTH CHINA	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	28
20DV	CNNSA	S. CH + HK + TW	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	4
40DV	CNQZH	S. CH + HK + TW	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	5
40DV	CNSHK	S. CH + HK + TW	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	61
40DV	CNTAO	NORTH CHINA	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	4
40DV	CNTXG	NORTH CHINA	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	19
40HC	CNXMN	S. CH + HK + TW	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	20
20RE	CNYTN	S. CH + HK + TW	ALBANIA	ALDRZ	BALKAN	05.01.2018 00:00	6
20DV	GBLGP	NWC + UK + IRL	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	1
20DV	IDSUB	SE ASIA	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	23
20DV	INHZA	N. INDIA + PAK	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	16
20DV	ITGOA	WEST MED	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	2
20DV	ITNAP	WEST MED	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	14
20DV	ITSPE	WEST MED	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	16
20DV	JPHIJ	JAPAN	ANGOLA	AOLAD	ANG + NAM	12.06.2018 00:00	1

Any assistance will be much appreciated.

[v-xicai · Accepted solution]

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.