Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |