Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BabyBinki821
Helper I
Helper I

Help - Cumulative Count of Customers who have made, 2,10, 20,50 and 100 orders throughout time

Hi

 

I have come up with a solution that counts the number of customers through their unique ID that have ordered at least 2, 10, 20, 50 and 100 orders. 

 

- Created a table using summarize to pull unique customer IDs and total orders:

Account Orders = Summarize(Orders,Orders[EC2 ID #],"Total Orders",[Total Orders])
 
- Then used this formula to get the number of customers who made at least 2 purchases
Accounts W/@2 Orders = Calculate(count('Account Orders'[EC2 ID #]),'Account Orders'[Total Orders]>=2)
 
The formula works and just repeated it for the other order amounts. My issue is that it only gives me a count as of now and regardless of time fram that I use cant see the number of customers increase over time since the beginning. 
 
How do I add the time piece to get the cumulative number over weeks?
12 REPLIES 12
Whitewater100
Solution Sage
Solution Sage

Hi:

Great point by Tamerj. One solution could be for you do the following. Add a calculated column to your customers table which is the measure that just counts number of orders.

E.G. Orders = COUNTROWS(OrderTable). If you want lifetime orders you can use this total measure. 

Now you will have Order Count for each customer in this table. 

Then another calc column in Customers Table.

Order Groups = SWITCH(
                                       TRUE(), 
                       Customer[Orders] <= 2, "0-2", Customer[Orders] > 50 && Customer[Orders] <=100, "50-100", 
                       Customer[Orders] > 2 && Customer[Orders] <= 10, "2 - 10", Customer[Orders] > 10 && 
                       Customer[Orders] <= 20, "11 - 20", Customer[Orders] >20 && Customer[Orders] <=50, "21 - 50", 
                      "Over 100")

and use this to bucket the customers. 

There is a video on doing this a couple of other ways that could be helpful.

https://www.youtube.com/watch?v=D1LO8syugMA

Whitewater100_0-1650030939249.png

 

Hi

 

Thanks for all the help guys.

 

So I have been going crazy all last night and early morning. Literally losing my mind. I also came to the conclusion that I do not need dynamic.

 

Whitewater's cumulative formula works. But I am getting a number of accounts that made >= 2 orders higher than the actual number of customers who made >= 2 orders. And in some of the time frames (2021/early 2022) - the number I am getting is higher than the customers that we have who have made an order.

 

For example, I am getting 250 customers who made =>2 orders at the end of 2021 but at the end of 2021 we only 220 customers total. When I look at the summarize table, the total count by the different metrics in terms of #of orders is correct, so something is happening when I pull from the past and gets smoothed out as I get closer to present day. So I wonder if the below formula doesnt work in terms of being used in the past? Or somehow related their is no time component in my summarized table? Also in the column that signifies orders in the order table has "CANCELED" OR "ORDERED," so maybe the formula is counting CANCELED orders?

 

Accounts W/@2 Orders = Calculate(count('Account Orders'[EC2 ID #]),'Account Orders'[Total Orders]>=2)

 

Another approach which may not be possible - Can I count directly from the order page? I have the customer ID # and orders. The only problem is that the orders column either notes "ORDERED" or "CANCELED." So I would need a filter to only count "Ordered."

Hi:

 

I think you are correct. You can tweak your initial measure.

Accounts W/@2 Orders = Calculate(count('Account Orders'[EC2 ID #]),'Account Orders'[Total Orders]>=2)

 

You may have to do a couple measures to get "Shipped Orders" and use this in the measures.

Without seeing your tables and columns you could count All Orders with a COUNTROWS and then calculate Cancelled ordered.  e.g. = CALCULATE[All Orders], FILTER(Order Table, OrderTable[Status] = "Cancelled") and subtract  Cancelled Orders from ALL Orders to arrive at Shipped Orders. 

Then use Shipping Orders in the previous calculations. 

 

If you have any other indication an order was shipped or is live in your orders table you could have just one measure to get to Shipping Orders.

 

You can still do the calculated columns to bucket your customers into order brackets of 02,2-10,11-20,etc..

Hi

 

So by using part of your approach above - I now have the correct number of Customers who made at least one order.

 

I created a measure - 

Accounts Ordered = calculate(DISTINCTCOUNT(Orders[EC2 ID #]))
I then added a visual order filter which has either ordered or canceled. When selecting canceled I get to the correct number of customers who have made at least 1 order.
 
How do I set-up the 2,10 and etc? I need to count the number of times that "ORDERED" appears and relate it back to the ID # and now struggling with this piece.

So update - I have further improved and used this so I no longer have to filter for "Ordered"

 

calculate(DISTINCTCOUNT(Orders[EC2 ID #]),filter(orders,Orders[Order Status]="ORDERED")
 
Now I just need to find a way to bucket the unique IDs by how many times "ORDERED" appears. I tried this but it didnt work -
 
calculate(DISTINCTCOUNT(Orders[EC2 ID #]),filter(orders,Orders[Order Status]="ORDERED" && ">1")
 

Hi:

A big consideration is how you set up your data model. A look at this would be most helpful. If you have a customers table,

1. Ideally, you have a separate customer table with their info and id, etc.

2.Create the measure that counts Net Orders. ( Not creating a measure for 0ver 2 over 10 ,over 20 etc..

3. Add a calculated column to customer table that is also called Net Orders.

  CC  Net Orders = [Net Orders]

 You will now see the number of orders by customer in the customer table.

4. Add another calc col to customers.

  

 

Order Groups = SWITCH(
                                       TRUE(), 
                       Customer[Net Orders] <= 2, "0-2", Customer[Net Orders] > 50 && Customer[Net Orders] <=100, "50-100", 
                       Customer[Net Orders] > 2 && Customer[Net Orders] <= 10, "2 - 10", Customer[Net Orders] > 10 && 
                       Customer[Net Orders] <= 20, "11 - 20", Customer[Net Orders] >20 && Customer[Net Orders] <=50, "21 - 50", 
                      "Over 100")

5. Use this new column in your visuals as you now how have groups of customers to report on as a sorting column. 

 

I hope this helps.

tamerj1
Super User
Super User

Hi @BabyBinki821 

how many customers do you have?

Whitewater100
Solution Sage
Solution Sage

Hi:

You need a Date Table to help with that. You can create a new table and use this code for a date table. This date table gets connected to your order(fact table) on order date to Dates[date]. It also gets marked as a date table with Table Tools option.

This one starts in 2021 and you can easily makes it cover more time and add additional columns to it like weeknumber.  Week N0. = WEEKNUM(Dates[Date])

A cumualtive measue usually goes like this:

Cumulative Accounts W/@2 Orders = CALCULATE([Accounts W/@2 Orders], FILTER(
ALLSELECTED(Dates), Dates[Date] <= MAX(Dates[Date])
)

DATES =

  GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

  )

)

Hi

 

Thanks! So I have date table and your DAX formula works ecept when I change the date in the splicer it restarts the counting. How do I get around this?

Hello:

Sorry just reading now. If you want Lietime Cumulative Sales you can use:

 

Cumulative Accounts W/@2 Orders = CALCULATE([Accounts W/@2 Orders], FILTER(
ALL(Dates), Dates[Date] <= MAX(Dates[Date])
)

I hope this is what you are looking for:-)

No worries - I appreciate you helping me. So it works from a cumulative perspective but I have more customers on a specific date than actual customers...

 

So then this must be wrong? Accounts W/@2 Orders = Calculate(count('Account Orders'[EC2 ID #]),'Account Orders'[Total Orders]>=2)

 

I am so lost right now

 

 

@BabyBinki821 
Actually this solution by @Whitewater100 is correct. The problem is with the Static Summary table which does not contain dates.
The summary table shall be useful in case you have multiple orders per customer in a single day or if you want to summarize that data on weekly or monthly levels (I guess in your case it is weekly level). Then you need to create the relationship between the summary table and the date table. Otherwise you need to rely on the original fact table.
However, I know that this still does not fulfill your requirement of having dynamic segmentation but achieving this remains one step forward towards the solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors