Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi @lemontree,
If possible please share your pbix file with us.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |