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

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
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ 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
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors