I have a list of clients with unique client IDs and each client belongs to a Client Group so out of 100 unique clients I have 30 unique client groups and want to calculate everyting on a group basis. (one client id can belongsto only one group name)
For each client ID I have daily balance data and from this I want to be able to calculate the sum of their balance and then filter my table /charts by Group to view how the Group performs.
I'd like to create a list of bands / bins and allocate each Group on their respective group.
I order to create the bands/bins I create and add a new table to my data model with the low, high and description of bands as explained in @MattAllington 's book "Learn to Write DAX".
In the fact table where I keep tha daily balances I go ahead and add a calculated column to calculate the Group Sales amount like this : Group Sales = CALCULATE([Total Sales]; ALLEXCEPT(lookuptable;lookuptable[GROUP NAME]))
(the lookup table contains all the cliend ids and the respective group names)
Next step is to allocate the bands to each of these group sales amounts and I try the following formula for another calc column:
CALCULATE(VALUES(BalanceBands[Band]);FILTER(BalanceBands;'Sales'[Group Sales]>=BalanceBands[Low] && 'Sales'[Group Sales]<BalanceBands[High]))
and i receive an circular dependency error message.
Any ideas on how to fix this??
thank you in advance for your time and support.
best regards,
Dimitris
