Create Column to Change Cost Center # based on the customer level to line with highest balance.

[darrigg]

Hi, I am trying to create a column to Change Cost Center # based on the customer level with highest balance. I want Account_Cost_Center to be the same on a customer level, based on the highest balance of that customer.

• the balance field is Acct_Ledger_Balance_MTD_AVG
• the customer level is Customer_CIS_Number
• and the account cost center is Account_Cost_Center
• it is coming all from "Query1

For example, in teh screenshot below, I want the "MAX COST CENTER" column to show the same:

• for customer 43 as 330.
• for customer 82 it should both be 331
• for customer 302 it should all be 548

this is what is generating 337 in the MAX COST CENTER column, but does not seem to work:

-------------

Max Cost Center =

var _max=MAXX(FILTER(ALL('Query1'),[Customer_CIS_Number]=MAX('Query1'[Customer_CIS_Number])),[Acct_Ledger_Balance_MTD_Avg])

return CALCULATE(MAX('Query1'[Account_Cost_Center]),

(FILTER(ALL('Query1'),[Customer_CIS_Number]=MAX('Query1'[Customer_CIS_Number])&&[Acct_Ledger_Balance_MTD_Avg]=_max)))

----------

Any idea?