Forum Discussion
Calculating # customers with single or multiple orders per period
- 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.
Hi I dont know if this helps, but i did the following: