Forum Discussion

abhishekc1's avatar
abhishekc1
Frequent Visitor
7 years ago
Solved

Total orders by category

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 1cl 11/1/2019100p1L1
OD 2cl 21/1/2019290p2L2
 OD 3cl 31/1/2019201P2L1
 OD 4cl 41/2/2019170P5L3
 OD 5cl 51/2/2019222P2L1
 OD 6cl 61/2/2019345p1L1
 OD 7cl 31/2/2019213p1L2
 OD 8cl 41/2/2019332P3L2
 OD 9cl 51/3/2019112P3L3
 OD 10cl 61/3/2019145P4L3
 OD 11cl 11/3/2019112P4L3

 

It has an active relationship with a productkey table

 

ProductCategory
p1cat 1
p2cat2 
p3cat 1
p4cat 3
p5cat 2
p6cat 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

General Comment
Show and Tell
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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's avatar
      abhishekc1
      Frequent Visitor
      7 years ago
      You’re a rockstar. Thank you so much and I’m terribly sorry I couldn’t reply earlier. Your solution worked like a charm.