topic Re: Get a histogram of customers grouped by total sales volume (sum of all order quantities) in DAX Commands and Tips

Get a histogram of customers grouped by total sales volume (sum of all order quantities)

hamslice — Tue, 02 Jan 2024 16:17:41 GMT

Hi,

I'm really struggling to get binning/grouping/tables etc to work, so would appreciate some guidance on doing the following:

What I'm trying (and failing) to do is get a count of the number of customer whose total quantity is, say, <5 or >100, or between 6 and 20... etc.

I already have the Sum of Quantity by Customer, available as a table/histogram or whatever. I've been able to create a 'count of customer by quantity' histogram, which I thought would be exactly what I need, but it appears to have evaluated the count of the number of orders of a particular quantity. I'm not interested in the orders, only the total number of units sold to them over a given period of time (currently looking at all time).

Customer	Quantity (sum of)
Customer A	2
Customer B	2
Customer C	7
Customer D	35
Customer E	60
Customer F	20
Customer G	9
Customer H	71
Customer J	4
Customer K	121

I'm thinking of binning the quantities as follows:

1-5

6-20

21-50

51-100

>100.

E.g. there are 3 customers with sales of 1-5 units in the table above.

Any help would be much appreciated. I've seen some similar queries trying to do a similar thing on count of orders, but not the sum of units from all of those orders, and have failed to adapt for my needs.

Re: Get a histogram of customers grouped by total sales volume (sum of all order quantities)

Anonymous — Wed, 03 Jan 2024 10:07:30 GMT

Hi hamslice ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a calculate column

Range = VAR temp = CALCULATE ( SUM ( 'Table'[Quantity (sum of)] ), 'Table'[Customer] = 'Table'[Customer] ) RETURN IF ( temp <= 5, "1-5", IF ( AND ( temp > 5, temp <= 20 ), "6-20", IF ( AND ( temp > 20, temp <= 50 ), "21-50", IF ( AND ( temp > 50, temp <= 100 ), "51-100", ">100" ) ) ) )

2. Use Range column as X-axis and Customer column as Y-axis

3.Final output

Best Regards,

Albert He

Re: Get a histogram of customers grouped by total sales volume (sum of all order quantities)

barritown — Wed, 03 Jan 2024 10:10:19 GMT

Hi hamslice,

Assuming you have [Customer] and [Quantity] columns in your original table, I could propose to create an additional table and then use it for the resulting histogram.

In plain text:

Table = ADDCOLUMNS ( SUMMARIZE ( Data, Data[Customer], "Sum of Qty", SUM ( Data[Quantity] ) ), "Category", SWITCH ( TRUE (), [Sum of Qty] <= 5, "1-5", [Sum of Qty] <= 20, "6-20", [Sum of Qty] <= 50, "21-50", [Sum of Qty] <= 100, "51-100", ">100" ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian