I have a report that aggregates account balances for each customer. The report includes a column with a maximum query calculation because I want to see the representative that is responsible for handling the customer. A few of the customers have multiple representatives, so I want another maximum query that reports the representative with the highest aggregate account balance. When I apply the query, I'm shown the maximum individual account balance instead of the maximum aggregate account balance.
The first expression below is the column I'm using for my max query. The second expression is my maximum query which reports maximum individual account balances, not the maximum aggregate. Any ideas on how to resolve this issue?
case
when = 'S'
then 0
when [Customer Max] > [Account Balance]
then [Account Balance]
when [Customer Max] > 0
then [Customer Max]
else [Account Balance]*[Customer Percentage]
end
maximum ([Balance] for [Customer])