Results For Lowest Level Dimension

[Anonymous]

Have a Data in this form:

Region	Country	City	Sales in millions	Quarter
North America	US	New York	434.5881266	Q1
North America	US	New York	593.5616063	Q2
North America	US	New York	203.5437846	Q3
North America	US	New York	212.2659167	Q4
North America	US	Canada	185.3205976	Q1
North America	US	Canada	592.3597726	Q2
North America	US	Canada	847.6435894	Q3
North America	US	Canada	70.37217548	Q4
Europe	UK	London	422.3300851	Q1
Europe	UK	London	773.7030397	Q2
Europe	UK	London	485.2654965	Q3
Europe	UK	London	583.7337934	Q4
Asia	China	Hong Kong	2.395353774	Q1
Asia	China	Hong Kong	572.2251346	Q2
Asia	China	Hong Kong	18.25943108	Q3
Asia	China	Hong Kong	146.5491327	Q4

Need to create a straight chart with lowest level Dimension (City) and show Sum of Sales for the Region like this:

City	Sum of Sales
New York	4812.207051
Canada	4812.207051
London	1900.36539
Hong Kong	2139.817331

But tried writing expression like:

=Sum({1} TOTAL <Region> [Sales in millions])

gives grand total in all rows

Tried:

=Aggr(Sum({<Country,City>} [Sales in millions]), Region)

But shows only one row for North America i.e. Honk Kong.

[sunny_talwar]

Try this:

=Sum({1} TOTAL <Country> [Sales in millions])

you don't really need 1 for this calculation to come through. Use one if this expression should not change results based on any selections.

HTH

Best,

Sunny

[sunny_talwar · Accepted solution]

Actually that did not work, try this instead:

=Aggr(Sum({1} TOTAL <Country> [Sales in millions]), City , Country) or

=Aggr(Sum({1} TOTAL <Region> [Sales in millions]), City , Country, Region)

[maxgro]

if you add the Region dimension and then hide Region in Presentation tab

this expression should work

=Sum({$} TOTAL <Region> [Sales in millions])

[anbu1984]

Check this app

[Anonymous]

Thank you sunindia, Massimo and anbu, they all serve the purpose. Since, I need to mark one as correct, I'd do it for sunindia since that came in first.

[sunny_talwar]

There is always 'Mark as Helpful' options to reward others for their contributions