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?
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).