locked
Measure for single and multi order customers. RRS feed

  • Question

  • I need some help creating a couple of measures. 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. So:

    # Single Order Customers
    # Multi Order Customers

    Note, this is not the same as New or Returning customers. In a given period there may be a returning customer (whose first order was outside the period selected) who has only placed one order.

    Also, I am actually doing this in PowerBI Desktop rather than PowerPivot however the crossover is so substantial that any solution here should work there also.

    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.

    Thursday, February 4, 2016 8:48 AM

Answers

  • You would have to iterate over the customers to do this. Your [minOrder] and [maxOrder] measures are fine (although the CALCULATE is not needed), and the number of customers (in the selected period) who placed a single order would be

    SingleOrderCount:=COUNTROWS(FILTER(VALUES(table[CustomerID]),[maxOrder]-[minOrder]=0)

    however, double check on customer with no orders. Both [minOrder] and [maxOrder] would be blank.

    Obviously,

    MultiOrderCount:=COUNTROWS(FILTER(VALUES(table[CustomerID]),[maxOrder]-[minOrder]>0)

    • Proposed as answer by Charlie Liao Thursday, February 18, 2016 9:45 AM
    • Marked as answer by Charlie Liao Monday, February 22, 2016 8:48 AM
    Thursday, February 4, 2016 5:05 PM
    Answerer