Tricky Set Analysis

[#1]

This is the script:

Person:
load * inline [
PersonId, PersonName, Country, PersonCount
1, 111, US, 1
2, 222, GB, 1
1, 111, US, 2
3, 333, RU, 1
4, 444, US, 1
5, 555, TW, 1
6, 666, CH, 1
7, 777, CH, 1
];

Countries:
Load * inline [
Country
US
GB
RU
TW
CH
];

CountryCounts:
Load * inline [
Country, CountryPower
US,1
GB,2
RU,3
TW,4
CH,5
];

I need to create a chart table like:

PersonId, PersonName, Country as dimentions

And as a fact - =sum(PersonCount*CountryPower)

I did this - =sum({$ <Country={$(=only(Country))} >}CountryPower * PersonCount)

It will work fine if you select one country only. But it doesn't for all.

Is there any idea how to work out the task?

Thanks

[#2]

why don'y you use just the exressions:

=sum(PersonCount*CountryPower)

[#3]

Not exactly sure what you are looking for, but I think the <aggr> could help you here because it can use the dimension as a parameter.

aggr ( [distinct | nodistinct ] [set_expression] expression {, dimension} )

Using your data it would look like:

Column

A: =sum(PersonCount*CountryPower)

B: aggr(Sum(CountryPower*PersonCount),Country)

C: aggr(NODISTINCT Sum(CountryPower*PersonCount),Country)

PersonId	PersonName	Country	A	B	C
			23	-	-
1	111	US	3	4	4
2	222	GB	2	2	2
3	333	RU	3	3	3
4	444	US	1	-	4
5	555	TW	4	4	4
6	666	CH	5	10	10
7	777	CH	5	-	10

[#4]

So the problem itself is not such simple. I've already found the solution for the task. But anyway thank you all for your help. I appreciate it.

Thanks