Calculate average and sum based on unique combination of other columns

[Anonymous]

Hi all,

I need to calculate the emission which is Propulsion / Distance. Quite is easy however 2 other calculations are nessecary which I could use your help with. 

1. Average of Distance: Every row has a duplictae due to the fuel type. This cannot be removed by simply removing the duplicates because propulsion has a unique value. I was thinking of calculating the average of distance based on the unique combination of Code, Name, Voyage No, report type and report date using distinct but so far had no succes. 

2. Sum of Propulsion: same as above but now sum instead of average. 

Below grid is an example of the data:

Code	Name	Voyage No	Report Type	Report Date Gmt	Distance	Fuel Type	Propulsion
2011	Gal	19001	Noon Report	31-01-2019 16:59	204	HFO	9.41
2011	Gal	19001	Noon Report	31-01-2019 16:59	204	MGO	0.00
2011	Gal	19001	End of Leg Report	31-01-2019 21:48	72	HFO	2.36
2011	Gal	19001	End of Leg Report	31-01-2019 21:48	72	MGO	0.00
2011	Gal	19002	Noon Report	02-02-2019 05:00	98	HFO	3.23
2011	Gal	19002	Noon Report	02-02-2019 05:00	98	MGO	1.22
2011	Gal	19002	Noon Report	03-02-2019 04:00	311	HFO	3.34
2011	Gal	19002	Noon Report	03-02-2019 04:00	311	MGO	12.10
2011	Gal	19002	End of Leg Report	03-02-2019 09:42	81	HFO	1.20
2011	Gal	19002	End of Leg Report	03-02-2019 09:42	81	MGO	2.69
2011	Gal	19002	Noon Report	04-02-2019 04:00	117	HFO	2.60
2011	Gal	19002	Noon Report	04-02-2019 04:00	117	MGO	1.20
2011	Gal	19002	Noon Report	05-02-2019 04:00	395	HFO	16.85
2011	Gal	19002	Noon Report	05-02-2019 04:00	395	MGO	0.00

So eventually it should show the below result:

Code	Name	Voyage No	Report Type	Report Date Gmt	Distance	Fuel Type	Propulsion
2011	Gal	19001	Noon Report	31-01-2019 16:59	204	HFO	9.41
2011	Gal	19001	End of Leg Report	31-01-2019 21:48	72	HFO	2.36
2011	Gal	19002	Noon Report	02-02-2019 05:00	98	HFO/MGO	4.45

Hope someone can help out!

Many thanks, 

Sharon

[ryan_mayu]

@Anonymous 

please check if the attachment is what you want.

[amitchandak · Accepted solution]

@Anonymous , Try to check this

sumx(summarize(Table, [Code], [Name], [Voyage No],[report type],[report date], [Distance]),[Distance])