Forum Discussion

maracles's avatar
maracles
Icon for Resolver II rankResolver II
10 years ago
Solved

Calculating # customers with single or multiple orders per period

I need some help creating a measure, hopefully this is the correct place. I need to use my dataset to create measures that tell me, within a given period, how many customers placed only 1 order, a...
  • MattAllington's avatar
    10 years ago

    you talk about a given period, but you seem to be missing a date column in your table. How do you know the period you are talking about?

     

    so putting period aside, I recommend you create a customer lookup table (called customers) that contains a single row for each customer.  Then join the table you have (I will call it Orders) to the customer table using a unique customer ID

     I don't believe you need the third data column. 

     

    Your first measure then would be (note I haven't tested it but I think it will work)

     

    Cust with 1 order =

    sumx(Customers,

            if(calculate(countrows(Orders)) = 1,1,0)

    )

     

    copy the pattern for the other one. 

     

    SUMX is an iterator. It creates a row context over the customer table. It takes one customer at a time. At each customer the CALCULATE function will cause the row context from the customer table to be converted to a filter context.  This then filters the Orders table so only orders for that 1 single customer are visible for the purpose of the calculation (for this one customer). If the answer for the single customer is 1 row, then 1 is added by SUMX. The process then moves to the next customer as SUMX iterates through every customer (one at a time) adding 1 for each customer that matches the rule (If 1 and only 1 row exists). 

     

    Hope me that makes some sense. Evaluation context is a complex topic and takes some time to learn.