Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
➤ 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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.