Total orders by category

[abhishekc1]

Hi All, Need your help with the following: My data looks like this- I have a data table that has the following fields:

Order IDCustomer idDateAmountProduct NameLocation

OD 1	cl 1	1/1/2019	100	p1	L1
OD 2	cl 2	1/1/2019	290	p2	L2
OD 3	cl 3	1/1/2019	201	P2	L1
OD 4	cl 4	1/2/2019	170	P5	L3
OD 5	cl 5	1/2/2019	222	P2	L1
OD 6	cl 6	1/2/2019	345	p1	L1
OD 7	cl 3	1/2/2019	213	p1	L2
OD 8	cl 4	1/2/2019	332	P3	L2
OD 9	cl 5	1/3/2019	112	P3	L3
OD 10	cl 6	1/3/2019	145	P4	L3
OD 11	cl 1	1/3/2019	112	P4	L3

It has an active relationship with a productkey table

Product	Category
p1	cat 1
p2	cat2
p3	cat 1
p4	cat 3
p5	cat 2
p6	cat 1

My requirement is to split the ordering customers into 2 buckets. Bucket 1) Customers who ordered 1-3 (total orders) times in a month 2) Customers with 4 (total orders) and more orders in a month Post this, i need to find out how many customers in each bucket order from the categories mentioned above in the product table. Any help will be deeply appreciated. Thanks in advance

[v-frfei-msft · Accepted solution]

Hi @abhishekc1 ,

One sample for your reference, please check the following steps as below.

1. Create a calculated column in order table.

Yearmonth = YEAR('Order'[Date]) *100 + MONTH('Order'[Date])

2. To create another calculated column to get the excepted result.

result = 
VAR coun =
    CALCULATE (
        COUNT ( 'Order'[Customer id] ),
        FILTER (
            'Order',
            'Order'[Customer id] = EARLIER ( 'Order'[Customer id] )
                && 'Order'[Yearmonth] = EARLIER ( 'Order'[Yearmonth] )
        )
    )
RETURN
    IF ( coun < 4, "ordered 1-3 ", "ordered more that 4" )

pbix as attached.

[abhishekc1]

You’re a rockstar. Thank you so much and I’m terribly sorry I couldn’t reply earlier. Your solution worked like a charm.