Calculate the Max value and perform a calculation on Max value for Summary

[kclark]

Hi Experts,

I feel I have tried EVERY nook and cranny for this calculation.  I need to find the Maximum(value) of two metrics for each record.  Then multiply those two maxes and finally total those.  For example,

ID   |   Dollars  |  Quantity
123 |  20.00     |      6
123 |  20.00    |       6
122 |  15.00    |       2
122    15.00    |       2

I should get this for Max (which I do):

ID    |   Dollars   | Quantity
123  |     20.00   |     6
122  |     15.00   |     2

Then this:

ID    |   Dollars   | Quantity |  Dollars * Quantity
123  |     20.00   |     6       |       120
122  |     15.00   |     2       |       30

Finally the sum of the calculated value:

ID    |   Dollars   | Quantity |  Dollars * Quantity
123  |     20.00   |     6       |       120
122  |     15.00   |     2       |       30

Total                                 |       150

In my query I have the data item as follows:

maximum([Dollars] for [ID]) * maximum([Quantity] for [ID])

I have set my Aggregate and Rollup properties to a variety between Total, Automatic, and Calculated.  I seem to still see the final number of ALL the records instead of the one max.  Anyone can help me?

Thanks!