Forum Discussion
Creating Buckets Based on Measure Results
I'm new to powerBI so some assistance would be very appreciated. I couldn't find a solution in the search, so hopefully, I am not duplicating effort.
In PowerBI I'm trying to visualize customer overdue balances and group them by thresholds.
See sample (Fact) table below:
SalesPerson | Customer | Balance | Outstanding Balance Tim | A134 | 2845 | 134 Tim | A134 | 3847 | 488 John | B395 | 8475 | 5 John | C113 | 100 | 0
I have created a measure to work out the Outstanding balance % per customer (Outstanding balance/Balance)
I want to be able to "Bucket" the results into 3 categories 0-5%, 5-10%, >10%. So I want to find out:
a) How many Customers fall into each bucket
b) How many salespeople fall into each bucket
c) The total amount of outstanding balance in each bucket.
Resulting table looks like this:
Bucket | No.Customers | No. SalesPeople | Total outstanding Balance 0-5% | | | 5-10% | | | >10% | | |
Of course, it needs to be dynamic on filters/slicers on the report.
I tried creating a summary table which sort of works however because of the relationship back to the fact table, I couldn't slice on salesperson or customer.
The end result would be a pie chart where the "buckets" would become the legend and the sum of the outstanding balance will be the values. And the same for a count of customers/salesperson.
Hi Dragonfury ,
- You need create a measure:
Outstanding balance % per customer = DIVIDE(SUM(Table_[ Outstanding Balance ]),SUM(Table_[ Balance ]))- After, create a calculate column:
Bucket = SWITCH(TRUE(), [Outstanding balance % per customer]>0.1,">10%", [Outstanding balance % per customer]<=0.05,"0-5%", "5%-10%")- Pie Chart result: Use calculate column Bucket for your legend
I hope it works for you, Best regards
1 Reply
- Bifinity_75Solution Sage
Hi Dragonfury ,
- You need create a measure:
Outstanding balance % per customer = DIVIDE(SUM(Table_[ Outstanding Balance ]),SUM(Table_[ Balance ]))- After, create a calculate column:
Bucket = SWITCH(TRUE(), [Outstanding balance % per customer]>0.1,">10%", [Outstanding balance % per customer]<=0.05,"0-5%", "5%-10%")- Pie Chart result: Use calculate column Bucket for your legend
I hope it works for you, Best regards