Skip to main content
cancel
Showing results for 
Search instead 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

Reply
timazarj
Helper II
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 IDCustomer name
AAAA
BBBB
CCCC

Line table

 

Line IDCustomer IDPurchased DatePurchased Expiry DateSalesProduct
1B2021-08-272022-11-057800PL
2C2021-08-272023-02-208000CL
3A2022-01-252023-03-301000PL
4B2022-02-242023-04-152000PL
5C2022-10-152023-10-15500PL
6A2023-01-022023-04-254500PL

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 IDTotal Sales
A5500
B2000
C8500

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

Thank you!

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

Sahir_Maharaj
Super User
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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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