Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
Thank you!
Although the matrix doesn't accept the sales bound as a row because it is a measure!
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:
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
56 | |
56 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
39 |