cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Group customers by number of sales in a dynamic date range

I have  a table of customer orders listed

Date Customer ID SalesID etc
I need to be able to group customers by the number of sales in a date range. For example the number of customers who had 1 order , 5 orders, 10 orders between x date and x date, with the ability to filter chart by a date range.

I can calculate for an individual customer and display in a table, as soon as I try an agregate by counting the customer all data is summarised.  What am I doing wrong?

1 ACCEPTED SOLUTION
Community Support

First create a table like below to have all group names. Sort "Number of Sales" column by "Index".

Then create the following measure.

``````Count of Customers =
VAR StartDate = MIN('Table'[Date])
VAR EndDate = MAX('Table'[Date])
VAR _table =
SUMMARIZE(
FILTER(
'Table',
'Table'[Date] >= StartDate && 'Table'[Date] <= EndDate
),
'Table'[Customer ID],
"Num Orders", COUNTROWS('Table')
)
RETURN
SWITCH(
SELECTEDVALUE('Groups'[Index]),
1, COUNTROWS(FILTER(_table, [Num Orders] <= 5)),
2, COUNTROWS(FILTER(_table, [Num Orders] > 5 && [Num Orders] <= 10)),
3, COUNTROWS(FILTER(_table, [Num Orders] > 10 && [Num Orders] <= 19)),
4, COUNTROWS(FILTER(_table, [Num Orders] > 19))
)``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

2 REPLIES 2
Community Support

First create a table like below to have all group names. Sort "Number of Sales" column by "Index".

Then create the following measure.

``````Count of Customers =
VAR StartDate = MIN('Table'[Date])
VAR EndDate = MAX('Table'[Date])
VAR _table =
SUMMARIZE(
FILTER(
'Table',
'Table'[Date] >= StartDate && 'Table'[Date] <= EndDate
),
'Table'[Customer ID],
"Num Orders", COUNTROWS('Table')
)
RETURN
SWITCH(
SELECTEDVALUE('Groups'[Index]),
1, COUNTROWS(FILTER(_table, [Num Orders] <= 5)),
2, COUNTROWS(FILTER(_table, [Num Orders] > 5 && [Num Orders] <= 10)),
3, COUNTROWS(FILTER(_table, [Num Orders] > 10 && [Num Orders] <= 19)),
4, COUNTROWS(FILTER(_table, [Num Orders] > 19))
)``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Frequent Visitor

With advise from Reza Rad I have been able to use a summary table within a measure to count the members by number of sales

```Count of Customers with that Many Orders =
var _CustomerOrders =
SUMMARIZE(
FactInternetSales,
FactInternetSales[CustomerKey],
'How Many Orders',
[Count of Orders]
)
return
COUNTROWS(
FILTER(
_CustomerOrders,
[How Many Orders]=[Segment Value])
)```

Which give me this result which responds to the date filter.

 Segmnet Count of Customers 1 15 2 5 7 3 19 1

Any advise on how to group the results as shown. Or even to just get a single card visual showng the number of ccustomers with more thatn 10 sales in the date range.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.