Get Total Value Regardless Selection

[#1]

Hi QV Expert!

I have this data:

Period	City	Score
2010	Jakarta	12
2010	New York	30
2010	Melbourne	5
2010	Madrid	58
2010	Tokyo	15
2010	Prague	6
2011	Jakarta	22
2011	New York	29
2011	Melbourne	4
... and so on	... and so on	... and so on

I have a trouble here, please help me how to accomplish this:

• X = Get AVERAGE VALUE of SCORE in a PERIOD from ALL CITIES regardless how many cities selected.

Thus, X will always 21 in selected period = 2010 even if selected city = Jakarta, Madrid

I have this formula, but it doesn't work.

=Avg({<Period={$(=GetFieldSelections(Period))}, City={$(=Concat{DISTINCT {1} City, ','})}>} Score)

Please help.

Thank you.

Attached 2 files I used to create the report:

1. sample_online.xlsx (take a look at the hardcode sheet)
2. test_sample_online.qvw (go to Report sheet)

[Sokkorn · Accepted solution]

Hi Adrian,

K.I.S.S

=Avg({$<City = >} Score)

Regards,

Sokkorn