cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## 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:

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.
Thanks
2 REPLIES 2
Helper II

Thank you!

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

Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ Want me to build your Power BI solution? Lets chat about how I can assist!

➤ Join my Medium community of 30k readers! Sharing my knowledge about data science and artificial intelligence

LinkedIn Top Voice in Artificial Intelligece, Data Science and Machine Learning