Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Background/relevan information:
My fact tables (list of columns):
Returns |
Return Issued Date (linked to Date col in Date table) |
Return ID |
Customer ID |
Marterial ID |
Category |
Subcategory |
Date Returned |
Return Quantity |
Units Shipped |
Sales Order ID |
ShipDate |
Order Type |
Sales Region |
Customer ID |
Material ID |
Shipped Quantity |
Dimension tables: Customer, Material, Date
Metric: return rate, which is the number of units returned/ number of units shipped X 100
Goal/What I'm trying to create:
1. A page containing line charts for specific set of products (X-axis is date in days and y-axis is return rate, percentage by day)
2. A table visual with column as the model/material and the 2nd column being the RMA rate for the product
3. Return rate slicer
Slicer Options |
0-0.99% Return Rate |
1-1.99% Return Rate |
2-2.99% Return Rate |
3-3.99% Return Rate |
4%+ Return rate |
4. Relative Date Slicer
Slicer Options |
Past Month |
Past 60 days |
Past 90 days |
Past Year |
The goal is to select, for example, 'Past 60 days' filter and '2-2.99% Return Rate' slicer options, which filter to line charts of products that have return rate between 2-2.99% for the past 60 days. The Product by Return Rate table should show the list of products with their return rates for the past 60 days, that are also between the selected rate range. For the line charts, he products that do not apply, will be blank and the table visual will omit the products that do not match the selected conditions.
WHAT I PROPOSE:
Creating a calculated table
Material Name | RMA Rate for Past 30 days | RMA Rate for Past 60 days | RMA Rate for Past 90 days | RMA Rate for Past Year | 30 Day RMA Bucket | 60 Day RMA Bucket | 90 Day RMA Bucket | Year RMA Bucket |
Product X | 4.23% | 3.49% | 2.13% | 2.04% | 4%+ | 3-3.99% | 2-2.99% | 2-2.99% |
I've created this measures sso far
Units Returned =
CALCULATE(
SUMX(
FILTER(Returns, Returns[Category 1] = "A" || Returns[Category 1] = "IB" || Returns[Category 1] = "C"), RMA[QTY]),
DATESBETWEEN('Date'[Date],DATEADD(LASTDATE('Date'[Date]), -1, MONTH), TODAY())
)
Units Shipped Past Month =
CALCULATE(
SUM(UnitsShipped[Qty]),
DATESBETWEEN('Date'[Date],DATEADD(LASTDATE('Date'[Date]), -1, MONTH), TODAY())
)
RMA Rate Past Month = DIVIDE([Returns Past Month], [Units Shipped Past Month], 0)
I'm unsure how to recreate this formula in a calculated column of my calculated table that would have the row context be for each product. I'm not sure if this is the right way to go about creating relative date and percentage slicers for the page in my report. Thanks for reading.
@Anonymous , if you are showing data by products, it will have row context of the product, else you can force by using summarize of values
sumx(summarize(table, table[product],"_1",[Units Returned]),[_1])
sumx(values( table[product]),[Units Returned])
User | Count |
---|---|
117 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
62 | |
55 |