Getting Totals for data outside of the filter

[scott_simmons]

For our sales team we have them setup so that a territory exists with in a region.  We produce reports every week that go out to the territory manager.  So the report is filtered to the territory, I want to show three metrics, the first two are easy it is the third I don't know how to do.

Structure / Roll up:  Company / Region / Territory

Expressions:

1: Sales  = sum(sales)

2: Sales as % of Total = sum(sales) / sum({1} sales)

3: Sales as % of Region = sum(sales) / ?

[Vegar · Accepted solution]

Try this:

= sum(sales) / sum({1<Territory, Region = {'$(=only(Region))'}>} sales)

[Nadi_Clarke]

sum(Sales)/ sum(total <Region> aggr(sum(Sales),Region))