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

[sunny_talwar]

May be try this:

If(Dimensionality() = 0, Avg(Aggr(Avg(Score), Supplier)), Avg(Score))

[MK_QSL]

Go to expression tab

Selection your expression

Go to Total Mode in the same tab

Select

Average of Rows

[#4]

Thanks for the tip. I tried it and it does work in this instance. The problem is that the data is a little bit more complex than that and that I also need some other aggregations (such as category code which a few suppliers belong and then I want the data to aggregate on the supplier level).

So unfortunately, still no luck...

[#5]

Thanks, but I don't see how that would work. A row average is what I am trying to avoid (since that would give me the wrong answer).

What I really would need would be some way to calculate the sum distinct (per supplier number) in the same manner as count distinct works. I thought the aggr would do that, but it only puts the value on the first instance the supplier number shows up...

[sunny_talwar]

Would you be able to share a more representative sample of the data you are working with? May be there is something which can be done to make it work.

[MK_QSL]

Provide sample documents which is not working as per our reply alongwith the result you are looking for..

[#8]

Hi,

Here is some more data (now with category). Hope this explains it better.

[#9]

Hi, added a new file

[#10]

Hi,

Added a new file with some more data.