Aggr function with Hierarchy group

[Anonymous]

Hi,

I am using a Aggr function on a dimension that I want it to dynamically change based on the Hierarchy selection from the Group that I created. Below is an example:

I have these two tables with fields like this:

Country | Sales | Margin

Region | Sub-Region | Country

Both these tables are associated by Country field and I have Geography group that has grouping of Region, Sub Region and Country. Now I am putting a (TreeMap) chart by the Geography group that showing Margin%. I am looking a function like this to use:

Aggr( sum(Margin) / sum(Sales), Geography)

and I want the Aggr function to work based on the Geography group level selected on the chart. If I define the Aggr() at Country level then I am not getting the desired output of Margin% for Region and Sub region level group selection. (it is giving a simple sum of all countries Margin% at the higher level).

Can someone please advice how I can make the Aggr() work with grouped dimension?

Thanks,

Balaji

[OmarBenSalem]

What happens if you only have sum(Margin) / sum(Sales) as an expression? without the aggr?

[Anonymous]

Hi Omar,

The actual expression I am trying to do require the Aggr() function to do a Product level and Geography level. Where Product is a single dimension while Geography is a grouped dimension. The above scenario I tried to give a simple form of what I am looking for something like this (still simplified but where the Grouped dimension base Aggr() is needed).

Sum(Aggr( (Sum(Margin)/Sum(Sales)) * Sum(Qty), Product, Geography) / Sum(Qty)

Thanks,

Balaji