Calculating # customers with single or multiple orders per period

[maracles]

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, and how many placed > 1 order.

The columns involved are:

customerID
orderID
orderCountCumulative

The first two columns are self explanatory, the third is a custom column which increments every time a customer places an order. For example the value for a customers first order would be 1, for the second order 2 and so forth. This is based on the unique customerID they have in the customerID column.

My basic approach to this problem has been attempting a measure that does as follows. Note that I have simplified my language to be more readable.This is also only the measure for Single Order Customers, the multiple measures follows the same logic.

var minOrder = CALCULATE(MIN[orderCountCumulative])
var maxOrder = CALCULATE(MAX[orderCountCumulative])

var orderDiff = maxOrder - minOrder

var singleOrderCount = CALCULATE(DISTINCTCOUNT[customerID], FILTER(table,orderDiff=0))

RETURN singleOrderCount


My logic behind this was that anyone placing a single order will have a minimum Order count of 1 and a maximum Order count of 1 therefore the differance is 0. Everyone else placed multiple orders.

The above doesn't however work because rather than look at the orderDiff for individual customerID's, it instead works out the difference using the max and min cumulative orders from ALL records in the given period.

Can someone explain how I would iterate through the table to count the order differance for individual customers OR to explain a simpler/different method for calculating what I want... I feel like I may have over complicated things!

My measure does work if I add customerID to the table rows because then it calculates per customer... this isn't useful though because I want to chat this cannot have the customerID as an axis.

Thanks in advance.

[RaminLayeghi]

Hi I dont know if this helps, but i did the following:

var

Customers=FILTER(SUMMARIZE(Orders,Orders[customer_id],"c",COUNT(Orders[id])),[c]=2)

Return

COUNTROWS(Customers)

it worked for me.

[MattAllington · Accepted solution]

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. 

[maracles]

Hi Matt, thanks for that I'm going to test it out now. As for the period there is a datePaid column which is linked to a dateTable, I was planning on just using that as a filter and not factoring it into the measure itself.

[MattAllington]

Ok, good so this will work with your calendar table on a chart axis for example. 

[maracles]

Brilliant, tested that and it worked. I already had the customer table just hadn't thought about using it in that way