Forum Discussion
RANKX or TOPN based on two fields
I have a summarized dataset I am using to create a scorecard for different products/categories.
The main data table is a list of products, the # of units sold, and the start date and end date of the month
Product # of Units Sold Start Date End Date
A1 50 Jan 1, 2019 Jan 31, 2019
B1 25 Jan 1, 2019 Jan 31, 2019
A1 60 Feb 1, 2019 Feb 28, 2019
B1 35 Feb 1, 2019 Feb 28, 2019
The measure that summarizes the # of units sold is a rolling six month average, using the filter function, and [Latest Score Date] and [Earliest Score Date] measures from my calendar table. These measures define the six month window.
Avg. Monthly # of Legs - Rolling Average:= CALCULATE(SUM('Product Scorecard KPI'[# of Units])/[# of Months - Total Score], FILTER('Product Scorecard KPI',
'Product Scorecard KPI'[Month End Date] <= [Latest Score Date] &&
'Product Scorecard KPI'[Month End Date] >= [Earliest Score Date])
This measure essentially returns the total # of units between Jan 1, 2019 - Jun 30, 2019, when filtered for July 1, 2019. (Feb 1 to Jul 31 when filtered for Aug 1, 2019, etc....)
I want to create a rankx measure that ranks these products based on two conditions
1. The product category (every product in the table above has a specific category it is grouped under)
2. The six month window defined in the measure above (based on the [latest score date] and [earliest score date])
I have seen rankx measures that are based on multiple criteria, but those criteria are always in the same table, and with a 1to1 relationship.
The product table contains the category, and the calendar table contains the dates, so they are different tables.
With each score dynamically being the rolling six month average, i also am not sure how to handle that.
All I am looking for really, is for my report that has 12 months showing for a bunch of different measures, I want some formula/filter i can apply to the page to narrow in on the top 15 products under each category in each month. Rankx and topn are both concepts i simply have not been able to wrap my head around, and I feel this example is a bit more complicated than average.
Any assistance would be appreciated. If any additional information is needed, I'll try and provide it.
The link you provided was actually the best solution I could find online as well. It seemed to be exactly what I wanted at first.
The issue is that the formula they create uses the ALL function to create the criteria table, and the all function can only reference fields in the same table. I want my rankx to be based on Year-Month (in the calendar table) and Product Category (in the product table). I don't have a direct relationship from the KPI table and the calendar table, so it has to be dynamic.
I ended up building a ranking table in SQL, added it to the data model, and joined it to the product and calendar tables. I changed my measures to reference that table, and show as blank if the rank of that product/month was >15, and did a sumx. so not perfect, but is working quite well for my hardcoded six month windows. If i ever need it to be dynamic, and rerank the top 15 for whatever timeframe i pick, this solution wouldn't work. But for now this does.
Thanks for the quick responses though.
4 Replies
Have you referred to this link
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601The link you provided was actually the best solution I could find online as well. It seemed to be exactly what I wanted at first.
The issue is that the formula they create uses the ALL function to create the criteria table, and the all function can only reference fields in the same table. I want my rankx to be based on Year-Month (in the calendar table) and Product Category (in the product table). I don't have a direct relationship from the KPI table and the calendar table, so it has to be dynamic.
I ended up building a ranking table in SQL, added it to the data model, and joined it to the product and calendar tables. I changed my measures to reference that table, and show as blank if the rank of that product/month was >15, and did a sumx. so not perfect, but is working quite well for my hardcoded six month windows. If i ever need it to be dynamic, and rerank the top 15 for whatever timeframe i pick, this solution wouldn't work. But for now this does.
Thanks for the quick responses though.
hi rmelvin
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
Hi rmelvin ,
If you need to get average [# of Units Sold] of rolling 6 month , you can try to create measures like DAX below.
Period End = LASTDATE(Table1[End Date]) Period Start= FIRSTDATE( DATESINPERIOD(Table1[End Date], [Period End], -6, MONTH)) Rolling 6M Avg = CALCULATE(AVERAGE(Table1[# of Units Sold]),DATESBETWEEN ( Table1[End Date], [Period Start], [Period End] ))You can also refer to the case learning about RANKX: https://community.powerbi.com/t5/Desktop/Fixed-ranking-of-percentage-measure/td-p/781420.
If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
