How to Rank "New Customer" Based on Order Value

[shafiz]

I have created a measure for monthly new customer. I don't have continuous date. I have only month and Year. My measure working fine to bring out the new customer using Except function but now I want to rank them. Let's say in January 2022 I have 10 new customer and I want to show only top 5 of them based on order value and quantity. How can I acheive this? This is the table.

Here is the measure  to calculate new customer:

   Var CurrentCustomers = VALUES('Intake Order'[Customer Name])

    Var CurrentDate = Min('Date Key Table'[Dates])

    Var PastCustomers = CALCULATETABLE(

        VALUES('Intake Order'[Customer Name]),

        ALL('Date Key Table'[Dates]), ALL('Date Key Table'[Year]),

        'Date Key Table'[Dates] < CurrentDate )

    Var NewCustomers = EXCEPT(CurrentCustomers, PastCustomers)

    Return COUNTROWS(NewCustomers)

I want the result in the below form.

There would be a slicer of Month-Year. When I select a specific month year, it will show me only the top 5 of the new costomer. New customer table is okay. Now I need to filter it. Any help would be very much appreciable. Thanks

[shafiz · Accepted solution]

Problem Solved😅 What I was missing actually I was trying to filter the new customer table based on Order value for all customer. That's why its showing me the blank instead of the top 5.

Created the measure which can calculate total order value for only new customer. Then filtered the new customer table with the newly created measure.

Here is the Measure for this solution:

"New Customers Order Value =

    Var CurrentCustomers = VALUES('Intake Order'[Customer Name]) -- Getting all the customer name till date

    Var CurrentDate = Min('Date Key Table'[Dates])

    Var PastCustomers = CALCULATETABLE(  -- Getting all the customer name before the current date

        VALUES('Intake Order'[Customer Name]),

        ALL('Date Key Table'[Dates]), ALL('Date Key Table'[Month Name]), ALL('Date Key Table'[Year]),

        'Date Key Table'[Dates] < CurrentDate

    )

    Var NewCustomers = EXCEPT(CurrentCustomers, PastCustomers) -- Mathemetically working like Intersection

Return CALCULATE([Total Order Value], NewCustomers)"

For new customer measure it is actually the same, Instead of calculate, needs to return countrows. Thanks to all who views the problem and @bolfri .

[bolfri]

Hi, can you use https://wetransfer.com/ with sample pbix file or a paste here a table which we can copy to help you? It will be easier.

Recomendation:

- even if you have only month and year you can create a date column eg first day of the month, so you can use dim_calendar and time inteligence functions (additional bonus for this is that with a lot of rows it will work faster)

- you can add new column with flag if this order makes customer a new one and then count only new for specific date (easier solution, better performence than you have)

- then having both of this you can use rankx with value and QTY only for new customers

[shafiz]

Yes, Date which I have already. Using this dates I itentified the new customer already. What I am unable to do is rank of those new customer. In 2nd solution, how to identity the order made is new? I have relation between date key table and order table. I can share you picture here:

Order Table:

Date Key Table:

Here is the table showing count of new customer.

Here is the new customer table, Now I want to filter this table for just top 5, Which is the ultimate goal :

I am unable to filter by Top 5 for unknown reason. I don't know what I am missing. Any help in this way or other would be appreciable. Feel like I am stuck some where and I can't move !!

[bolfri]

Due to fact that you can't provide any sample data I've created my own.

I've added a new column that has flag: NEW/OLD for each row.

New Customer Flag = 
var current_customer = Fact_Table[Customer Name]
var first_order_date = MINX(FILTER(ALL(Fact_Table),Fact_Table[Customer Name]=current_customer),Fact_Table[OrderDate])
return IF(Fact_Table[OrderDate] = first_order_date,"NEW","OLD")

It tells me if this is the first order of that customer. If one customer buy in same day, we will by suming thier OrderValue for our rank, but if one customer buy two times in one month (eg customer A in Jan) we will be counting only first order from 1st Jan and excluding order from 15th Jan.

Then we can create a measures like:

Number of customers = DISTINCTCOUNT(Fact_Table[Customer Name])

New Customers = CALCULATE([Number of customers],Fact_Table[New Customer Flag]="NEW")

% Share of new customers = DIVIDE([New Customers],[Number of customers])

For your ranking you can use a measure:

Value = SUM(Fact_Table[OrderValue])

and then config a visual with TOPN elemens 🙂

 PBIX file:

https://we.tl/t-J8TlP0QX0G

[shafiz]

Thanks @bolfri . It's a good solution. But I cann't exclude any order value. I need to sum all the order value for that specific customer. Because that's what I need to compare along with order quantity. 🙂

[bolfri]

I don't understand. We are not excluding any values. Customer A has 19 order value, thats why he's top 1.