How to make measure for customers with x amount of orders per day (to be used line chart)

[lowerCamelCase]

Hello! First post here!

Just starting to learn PowerBi and still a bit lost in what is measure and when to use vs colums, so bare with me..

I will try to simplify my case, for this to be useful to other beginners. I want a line graph where on x-axis is dates and on the y-axis is 4 sets of data. Datapoints for each line represent sum of customers with x amount of orders. This graph would help to answer some questions, example: Which days people with 3 orders shop most likely? How much traffic is there in our shop, if we stop selling items that people with one order buy? etc..

The tables i have: Time Dimension and Orders. Linked by date in one-to-many, so Time Dimension table has date only once and Orders could contain multiple rows with the same date.

Orders
Date		Customer ID
1.1.2022	Saturday	A
1.1.2022	Saturday	B
1.1.2022	Saturday	B
1.1.2022	Saturday	C
1.1.2022	Saturday	C
1.1.2022	Saturday	C
2.1.2022	Sunday	B
2.1.2022	Sunday	B
2.1.2022	Sunday	B
2.1.2022	Sunday	A
2.1.2022	Sunday	A
2.1.2022	Sunday	A
3.1.2022	Monday	C

Time Dimension Table
Date	Day
1.1.2022	Saturday
2.1.2022	Sunday
3.1.2022	Monday

And the result should be:

Result		Customers (total) with () orders
Date	Day	1	2	3	4
1.1.2022	Saturday	1	1	1	0
2.1.2022	Sunday	0	0	2	0
3.1.2022	Monday	1	0	0	0

Could this be built in four measures that i could drop to line graph with x-axis containing dates? And maybe sum these measures so that one line in graph would represent several groups (meaning: leave out customers with only 1 order, so Saturdays result would be 2 customers, Sunday 2 customers and Monday 0 customers)?

How would you approach this?

[amitchandak · Accepted solution]

@lowerCamelCase , I think you need bucketing on the number of orders

I discussed one use case here - Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

[lowerCamelCase]

Hey @amitchandak !

I think this solution will benefit people a lot! How ever, i'm stuck at the first minutes of the video. I'm trying to give this question more generalized context, so that i and others will understand of the concept that can be applied to other type of problems. The real data that i have is not actually "Orders", so there is no "Quantity" with integer.

Data on video:

My Data (Actually customers also have CustomerHash and some of them are missing name and some names have two or more CustomerHash)

Orders
OrderHash	Date	CustomerID
SF644DG31XF8G1XFG38	1.1.2022	A
SF644DG31XF8G1XFG39	1.1.2022	B
SF644DG31XF8G1XFG40	1.1.2022	B
SF644DG31XF8G1XFG41	1.1.2022	C
SF644DG31XF8G1XFG42	1.1.2022	C
SF644DG31XF8G1XFG43	1.1.2022	C
SF644DG31XF8G1XFG44	2.1.2022	B
SF644DG31XF8G1XFG45	2.1.2022	B
SF644DG31XF8G1XFG46	2.1.2022	B
SF644DG31XF8G1XFG47	2.1.2022	A
SF644DG31XF8G1XFG48	2.1.2022	A
SF644DG31XF8G1XFG49	2.1.2022	A
SF644DG31XF8G1XFG50	3.1.2022	C

So with this data i can't use SUM on the first step: Total Qty = Sum(Sales[Qty]), because the rows that i have are string (CustomerID). Well, i can use COUNT, to count the rows. Results in the Tables and measures from the Orders table to check the table gives wanted result.

First table 

//Get the sum of the of the rows in the context of Quantity, so 25 days with 1 order etc.. Sum of these matched with distinct count of OrderID's (all rows) in Orders table  

1 Sum of Customer Qty = SUMX(FILTER(VALUES('Customer'[CustomerID]), [Total Qty] = MAX('1 Qty Bucket'[Value])), [Total Qty]

Second table

// Sum of quantities in the context of customer

1 Sum of customer Qty1 = SUMX(VALUES('Orders'[Customer]), IF( [Total Qty] = 1, 1, BLANK())) etc.. Sum of these matches the measure Distinct count of CustomersID of the Orders table.

The visuals and sum of these are work in progress, so if @amitchandak  can point me to other his great Youtube videos that will help with this case, it would be highly appreciated!

For this problem this was solution, so we can consider it solved! Thank you @amitchandak !!!!