Sales before and after discounts and shipping costs sorted by country

[GabbyGla]

Hello all, 

I need to have some measures. But acutally I really do not know where to start 😞

I have an export from our customer where we get the information like this: 

Customer Name	Article Nr	Article Name	Quantity	Price	Sourcing Price	Country	Discounts	Shipping
BradPitt	1234	T-Shirt	1	50€	25€	Germany	10%	5€
AngelinaJolie	1234	T-Shirt	1	50€	25€	Belgium	10%	5€
AngelinaJolie	4321	Socks	1	10€	2€	Belgium	10%	5€

There are some stuff I needed to show in one Matrix

1. Sales before discounts and before shipping costs sorted by Country  

2. Sales after discounts 

3. Sourcing price sorted by country 

4. Sales after discounts and Shipping costs (and this one is tricky- see*) 

Country	1. Sales in €	2. Sales after discounts	3. Sourcing Price Sum per Country	4. Sales in € after discounts and shipping costs
Germany	50€	45€ (=50€ - 10%)	25€	=45€-5€
Belgium	60€	54€	27€	=54€-5€ *

*For the shipping costs I need to just take ONE times shipping per Customer. So Angelina Jolie here bought two quantities and my data sheet says one shipping costs per row. But that is wrong since those two items could be shipped together. 

I hope it is clear and praying for some help 

Thank you, 

Gabby 

[ryan_mayu]

@GabbyGla 

pls see the attachment below

[GabbyGla]

@ryan_mayu  Thank you so much for your fast solution. Everything was perfect except one point I did not point out so well: 

For the point 4. Sales after discount and shipping I can not take the average since I have like 400 different customers which all paid e.g. 5€ for shipping. 

Some of those 400 customers ordered just one article, some of them ordered a few. But actually of course one customer just needs to pay shipment costs once. So if I just take the average I will just have 5€ as shipment costs which is not right. But if I take the sum of the shipment costs I will have costs for every article which is not right as well. 

So I need shipment costs as a sum but only for every single customer not for every article sold. 

Is that clear oder should I explain it better?  🙂 Thank youuu! 

[ryan_mayu · Accepted solution]

@GabbyGla 

I try to added to records to test the last formula. pls see the attachment below.