cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors