Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
User | Count |
---|---|
105 | |
69 | |
48 | |
47 | |
47 |