Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Create filter based on rates by relative date range measures

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 NameRMA Rate for Past 30 daysRMA Rate for Past 60 daysRMA Rate for Past 90 daysRMA Rate for Past Year30 Day RMA Bucket 60 Day RMA Bucket 90 Day RMA Bucket Year RMA Bucket 
Product X4.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.

1 REPLY 1
amitchandak
Super User
Super User

@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])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.