How to sum group by high level?

[Anonymous]

For example, there are two tables,

City_Sales:
LOAD * INLINE [
Country,State,City,City-Sales
C1,S1,C1,5000
C1,S1,C2,8000
C1,S2,C3,7600
:
:
];

State_Sales:
LOAD * INLINE [
State,State-Sales
S1,35000
S2,87500
:
:
];

They are auto-joined by field "State". But the sales in table "State" is not that sum from sales in "City", they are unrelated. But I want to get Country-Sales by sum State-Sales.

I want to show this

Country State City City-Sales State-Sales Country-Sales
C1 S1 C1 5000 35000 122500
C1 S1 C2 8000 35000 122500
C1 S2 C3 7600 87500 122500
:
:

If just use "sum(State-Sales)" as a expression for column "State-Sales", there will be wrong. What expression is correct?(State-Sales and Country-Sales)

[tresesco]

i don't know what do you mean by "there will be wrong"? if it is that you getting from debugging, may be you are missing a "Group By" , which is mandatory for any aggregation function in the script.

Regards, tresesco

[#3]

Hi John,

Try using the following expression to get the Country total -

=Sum(TOTAL<Country>[State-Sales])

The TOTAL keyword will remove all dimensionality for the expression, but the <Country> will reintroduce the Country dimension, thus giving a total specific to each different Country value.

I hope this makes sense.

Thanks

Nick