Customer Segmentation based on Total Sales using Numeric Parameter

[timazarj]

Hi,

I need to dynamically categorize the customer if they have a certain amount $ of sales.

Customer Table

Customer ID	Customer name
A	AAA
B	BBB
C	CCC

Line table

Line ID	Customer ID	Purchased Date	Purchased Expiry Date	Sales	Product
1	B	2021-08-27	2022-11-05	7800	PL
2	C	2021-08-27	2023-02-20	8000	CL
3	A	2022-01-25	2023-03-30	1000	PL
4	B	2022-02-24	2023-04-15	2000	PL
5	C	2022-10-15	2023-10-15	500	PL
6	A	2023-01-02	2023-04-25	4500	PL

an active customer is a customer who has a purchase expiry date within the date filter.

 The total sales measure calculates the sales per each customer:

Customer Sales = Calculate ( Sum(Line[Sales]) , ALLEXPECT(Customer Table, Customer Table[Customer ID]))
Result for 2023 active customer

Customer ID	Total Sales
A	5500
B	2000
C	8500

End users need to see how many customers have sales over XX amount. And they prefer to be able to change that XX.

I am thinking of numeric parameters:

VIP Sales Bound = GENERATESERIES(1000, 70000, 500)

VIP Sales Bound Value = SELECTEDVALUE('VIP Sales Bound'[VIP Premium Bound])

Then I created a column for the Customer Table as:

Sales Bound = Switch ( True(), [Customer Sales]>=[VIP Sales Bound Value], "Sales>=$" & [VIP Sales Bound Value],  [Customer Sales]<[VIP Sales Bound Value], "Sales<$" & [VIP Sales Bound Value])

it must be added to the table because they want to see it as a matrix per Sales bound and Product.

Please guide me.

Thanks

[timazarj]

Thank you!

Although the matrix doesn't accept the sales bound as a row because it is a measure!

[Sahir_Maharaj]

Hello @timazarj,

To dynamically categorize customers based on their sales amount and show it in a matrix per sales bound and product:

1. Create a new table for the Sales Bound:

Sales Bound = GENERATESERIES(1000, 70000, 500)

2. Create a new measure for the Total Sales per Customer:

Total Sales = SUM(Line[Sales])

3. Create a new measure for the Sales Bound per Customer:

Sales Bound =
VAR CustomerSales = [Total Sales]
VAR VIPSalesBound = SELECTEDVALUE('Sales Bound'[Value])
RETURN
SWITCH(TRUE(),
CustomerSales >= VIPSalesBound, "Sales>= $" & FORMAT(VIPSalesBound, "#,##0"),
CustomerSales < VIPSalesBound, "Sales<$" & FORMAT(VIPSalesBound, "#,##0"))

4. Create a new matrix visual with fields:

• Rows: Sales Bound
• Columns: Product
• Values: Count of Customer ID or Total Sales (depending on your preference)

This will show the number of customers or total sales for each sales bound and product combination.

5. Add a slicer for the VIP Sales Bound parameter table to allow end-users to select the sales bound value.

With these steps, you should be able to dynamically categorize customers based on their sales amount and show it in a matrix per sales bound and product.

Let me know if this works for you.