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

[hamslice]

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.

[Anonymous · Accepted solution]

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

[barritown · Accepted solution]

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

[Anonymous · Accepted solution]

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