Forum Discussion

Dragonfury's avatar
Dragonfury
Frequent Visitor
3 years ago
Solved

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

  • 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