Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
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 -
So update - I have further improved and used this so I no longer have to filter for "Ordered"
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.
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.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |