Average for aggregate data

[#1]

I have a table of data that consists of suppliers, where they supply and a score (plus a lot more, but irrelevant for this question). What I'm trying to do is to calculate the average score per supplier and present it in a straight table. I also want the user to be able to display the information per site or per supplier.

1. A supplier can only have one score regardless of how many sites it delivers to
2. A total average need to be calculated and the average should disregard the number of sites (i.e. a supplier that only supplies to one site will have the same weight as one supplying to 3 sites)

This should be straight forward, but I get the wrong result. Either I am able to get the correct average by using an aggregate, but then the score will only show up on one site or I am able to getthe correct score per site, but then the average is screwed up...

Below is how I want it to show up (the first is the raw data and the the last two tables my desired result), but I don't get it to work. I'm attaching a small file with the data.

Any ideas on how to solve it (I think it should be really easy and get really frustrated that I can't solve it myself...)

Supplier	Site	Score
AAA	1	5
AAA	2	5
AAA	3	5
BBB	1	2
CCC	1	2
DDD	2	4
DDD	3	4
EEE		5

Supplier	Site	Score
Average		3,6
AAA	1	5
AAA	2	5
AAA	3	5
BBB	1	2
CCC	1	2
DDD	2	4
DDD	3	4
EEE		5

Supplier	Score
Average	3,6
AAA	5
BBB	2
CCC	2
DDD	4
EEE	5

[MK_QSL]

What result you are looking for?

[#3]

The average for the categories shoud be:

Orange: 3.5

Apple: 3.5

Banana: 4

The average per site should be:

Site 1: 3

Site 2: 4.5

Site 3: 4.5

The full total should be 3.6.

And when I choose to show the suppliers, the correct value should be shown in each cell.

Or to put it in more general terms - the averages should always be based on the supplier and a supplier should only be regarded once (even though it may supply several sites).