Manipulate the group by of aggregation

Arif shah

<p>Hi,</p><p> </p><p>If I want to add aggregation to chart and manipulate its group by functionality, how can I do that? For example</p><p> </p><p>I have a chart the shows the revenue per city. Now I want to add another reference line that shows goes across the city but this line shows the average revenue per region (region in which the city is located) where</p><p> </p><p>Region average = sum(revenue for all cities within the region)/ (number of cities within the region)</p><p> </p><p>Now we don't have a region dimension on the chart but we have cities/towns on the axis  and for each town the line should show the relevant region average revenue</p><p> </p><p>For example, If We have Newton, Watertown and Weston plotted on the x axis, then the average line should show the average revenue for Massachessts against these three towns and as soon as there is a city that will be from another region, the average line should change according to that city</p><p> </p><p>My question is how to do the average that is grouped by region when we have city on the x axis. I could have done this easily in tabelau and qlikview but not sure how to manipulate the grouping by of aggregation in Birt</p><p> </p><p>Arif</p>

kclark

<p>Hi Arif, I've made a simple example, I'm sure it could be better but it shows how I used a secondary axis (line) to show the average payments per state on top of the primary axis (city).  If this isn't what you were looking for could you create an example of your report using the classic models DB and post it here for me to work with.  Thanks!</p>