Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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
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)
if you add the Region dimension and then hide Region in Presentation tab
this expression should work
=Sum({$} TOTAL <Region> [Sales in millions])
Check this app
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.
There is always 'Mark as Helpful' options to reward others for their contributions