Forum Discussion
Customer Segmentation based on Total Sales using Numeric Parameter
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:
2 Replies
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.
Thank you!
Although the matrix doesn't accept the sales bound as a row because it is a measure!
