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!