Calculation of Aggregate of Aggregates

[anupamaMatta]

In the attached picture,
We have two groups. One at 'deal' level (white background) and the second at 'portfolio' level (grey background). We need to summarize values to 'minimum' at the 'deal' level, which is the lowest group and we need to sum, these minimum values(from the deal level) at the 'portfolio' level. We are able to do this.

But when we require to create a overall list summary row, as an average of the summaries at the 'portfolio' level, we fail to get that.

To illustrate


Initial data set is

Portfolio Deal measure
1 a 10
1 a 10
1 b 20
2 a 5
2 b 5
2 b 30


What we want and are able to achieve this

Portfolio Deal Measure

1 a 10
1 b 20
--------------------------------------------------
1 30 (10+20)
--------------------------------------------------
2 a 5
2 b 5
--------------------------------------------------
2 10 (5+5)
-------------------------------------------------

But we fail to get an overall aggregate row as below

Portfolio Deal Measure

1 a 10
1 b 20
--------------------------------------------------
1 30 (10+20)
--------------------------------------------------
2 a 5
2 b 5
--------------------------------------------------
2 10 (5+5)
==============================
Aggregate 20 (= 30 + 10/2 )
==============================


But instead what we are only able to get is

Portfolio Deal Measure

1 a 10
1 b 20
--------------------------------------------------
1 30
--------------------------------------------------
2 a 5
2 b 5
--------------------------------------------------
2 10
==============================
Aggregate 13.xx ( = 10+10+20+5+5+30/6 )
==============================

Please let me know if this is unclear.

Thanks

 

[blom0344]

How about:

total(minimum[measure] for [Portfolio],[Deal])/count (distinct [Portfolio])

?

Ties Blom