Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lemontree
Helper I
Helper I

Issue with Banding

 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

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @lemontree,

 

If possible please share your pbix file with us. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft i will need to add some dummy data and do it. thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.