Creating Buckets Based on Measure Results

[Dragonfury]

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.

[Bifinity_75 · Accepted solution]

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