create measure to evaluate customer based on sales rank and number of active days etc.

[Anonymous]

In short my problem is that I am trying to recreate what I did with calculated column, and change it to calculate measures.

My goal is to find high value customers (HVC) from the order table using three crterias.

1. sales
2. number of active days 
3. average order value

   To achieve this I have created a customer lookup table from order table, how I implemented this was:

   • Sales
     1. create a column to sum the sales 

sales = CALCULATE(SUM(orders[sales]),FILTER('orders','Customer lookup'[customer ID]=orders[customer ID]))​ 

• create a column rank the customers based on sales

sales rank = RANKX('Customer lookup', 'Customer lookup'[sales]) 

• create a column to calculate the running total 

sales running total = CALCULATE (
    SUM ( 'Customer lookup'[sales] ),
    ALL ( 'Customer lookup' ),
    'Customer lookup'[sales rank] <= EARLIER ('Customer lookup'[sales rank] )) 

• create a column to calculate customer status based on cut-off percentage on running total

sales status = IF('Customer lookup'[sales running total]/SUM('Customer lookup'[sales]) < 0.7, "A",IF('Customer lookup'[sales running total]/SUM('Customer lookup'[sales]) >= 0.7 && 'Customer lookup'[GMV running total]/SUM('Customer lookup'[sales]) < 0.9, "B", "C"))<p>In this way I can find the top customers that contributed 70% of the sales.</p><p>For <strong>number of active days it's the same logic, except for the first column would be:<li-code lang="javascript">CALCULATE(DISTINCTCOUNT(oders[Order Date].[Date]),FILTER('orders','Customer lookup'[customer ID]=orders[customer ID]))

So in the end I would also have a [order days status] column. 

• average order value
  1. create a column to calculate average order value

sales per order = CALCULATE(SUM(orders[sales])/DISTINCTCOUNT(orders[Order ID]),FILTER('orders','Customer lookup'[customer ID]=orders[customer ID])) 

• create a column to calculate customer status based on the median of average order value

average order value status = 
IF( 'Customer lookup'[sales per order]> MEDIAN('Customer lookup'[sales per order])*1.25,"A",IF('Customer lookup'[sales per order]>MEDIAN('Customer lookup'[sales per order])*0.75 && 'Customer lookup'[sales per order] >= MEDIAN('Customer lookup'[sales per order])*1.25, "B", "C"))​<p>And at last, find the HVC based on the three status, if there are at least 2 As, then it's a HVC.<li-code lang="javascript">HVC = If(CALCULATE(
SUMX(
UNION(
	SELECTCOLUMNS('Customer lookup',"ContainsA", 'Customer lookup'[sales status]),
	SELECTCOLUMNS('Customer lookup',"ContainsA", 'Customer lookup'[sales per order status]),
	SELECTCOLUMNS('Customer lookup',"ContainsA", 'Customer lookup'[ordered days status])		
)
,IF([ContainsA] = "A", 1, 0)))>1, "YES", "NO")

So now I want to create a measure to replicate what I did with calculated columns. Because I want to use the measure against Months, to see the number of HVC in each months. And another measure to see how many customer has changed from HVC to non-HVC, this is the main challenge I couldn't achive with the calculated columns.

Any help would be highly appreciated, thanks a million in advance!

[Anonymous]

Hi @Anonymous ,

It is hard for us to understand your code without data table. Could you share a sample file with us and show us a screenshot with the result you want? This will make us easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.