Aggregating client sales and then bucketing and creating bar charts of the results

[Wheaten22]

Hi,

My dataset contains thousands of rows of Orders which contain Client IDs and the amount they purchased for as well as about fifty other columns that are not important for what I am trying to do here.  

What I want to do is:

1. Aggregate the sum of all sales by the client ID
2. Bucket the aggregated spend by client (i.e. 0-10,000, 10,000 - 25,000, 25,000 - 50,000...)
3. Show a bar chart of how many clients fall into each bucket.  

I have been able to do 1 & 2 multiple different ways and in a table it looks good, but when I try to display it in a graph, it is not coming out correctly.  The closest I have come is the bar chart working but it is counting each individual order and bucketing them instead of the aggregate by client.

Thanks in advance for your help!

[Anonymous · Accepted solution]

Please follow the below steps.

1. To aggregate the Amount based on Client ID

Group the Client ID column by applying SUM on the Amount column.

= Table.Group(#"Changed Type", {"Client ID"}, {{"Amount", each List.Sum([Amount]), type number}})

2. To create a bucket of Amount   -    Create a conditional column as below

= Table.AddColumn(#"Grouped Rows", "Bucket", each if [Amount] < 50 then "0 - 50" else if [Amount] <= 100 then "50 - 100" else if [Amount] > 100 then "100 - Above" else null)

3. Using the newly created conditional column create a bar chart.