September 24, 2015 at 5:00 pm #1851
I’ve read a lot of posts about RANKX, but I’ve not found anything to help with what I’m trying to accomplish. On the attached file, I would like to include a rank for each of the measures shown that ranks the 5 regions against each other & ranks all 58 branches against each other. Is there a formula solution for my current data model?September 24, 2015 at 6:05 pm #1854
Just downloaded workbook. Let me see if I understand your needs. I am picturing a pivot that has Regions and Branches on rows; on the Region level you have a ranking between regions based on a given measure; when drilling down to the branch level, you have a ranking for each branch and that ranking is not constrained by the parent Region, but instead the ranking is against all other branches in all other regions.
If so, I have done something like that using “IF ( ISFILTERED ( ” as a check before evaluating the measure so appropriate filters can be set or cleared.
I am heading into a meeting shortly and may not be able to follow up right away with an example; but sometime later today or early tomorrow would be able to give example.September 24, 2015 at 6:42 pm #1856
Yes, you’re understanding of what I’m trying to accomplish is correct. I look forward to seeing your example. Thank you for taking the time to help.September 25, 2015 at 9:20 am #1859
Please see attached workbook (original worksheet was not removed, just hidden).
Using this blog by Rob Collie, I added the following measure and created a worksheet with pivot to demonstrate use:
Rank by Region or Branch:=IF (
ISFILTERED ( Branch[BranchName] ),
RANKX ( ALL ( Branch ), [RmrByService – RMR Sum] ),
ISFILTERED ( Branch[RegionName] ),
ALL ( Branch[RegionName] ),
[RmrByService – RMR Sum]
)September 25, 2015 at 2:46 pm #1864
I thought I replied yesterday thanking you, but I don’t see it. This worked beautifully – thank you!
I wonder if you might be able to help me once more. I tried extending this formula to also rank all employees amongst each other, but the measure is not behaving as I expected. I lent my book by Rob to a coworker which I really need to get back and reread because I still don’t have a firm grasp on filter contexts.
Would you mind reviewing my measure titled ‘Rank RMR ALL Emp-Br-Rgn’ to see what corrections I need to make?September 25, 2015 at 3:02 pm #1866
I’m sorry Tom, I posted this question on another forum as well and got confused about where the solution I tried came from. I haven’t tried your suggestion yet, but I’m going to work with it now.September 25, 2015 at 6:20 pm #1869
The difficulty you are working with is traceable to that the relationships and roles of Branch and Region in the model should be simplified.
As far as the last workbook goes, since the data table has a “primary sales rep no” column, relationships should go upward from the data table to the employees table to what is currently the Branch table (renaming the table to something like “Employee Branch”). You could then get rid of the redundant columns “Primary Sales Rep Branch” and “Primary Sales Branch Name”, as well as avoid potential synchronization issues between them.
I recommend a second Branch table to handle the relationship between RmrByService[Branch] and Branch. Although physically those records may be identical to an “Employee Branch” table, conceptually they are different. This would also eliminate the redundant column for Region and also avoid potential synchronization issues.
I do not often recommend identical record sets, but in this case the trade-off would be a model that is conceptually easier to work with and more reliable–and you would have less grief with formulas.
I would be glad to discuss further.
Note: From looking at the RANKX formulas in the workbook, I think the use of the third parameter is unnecessary (when you get your book back, see pages 148, 149 in “DAX Formulas for PowerPivot”).September 25, 2015 at 9:25 pm #1870
Tom, your solution to my original request was very intuitive and helped me (along with adding a missing relationship to my employee table) to develop a solution that works great. This is bound to be replicated in many of my reports and draw a lot of positive feedback, so I’m very grateful for your help. You can see my solution on the ‘Rank RMR ALL Emp-Br-Rgn’ measure in the attached file.February 4, 2016 at 1:03 pm #3272
- Started: 0
- Replies: 2
- Total: 2
I have a RankX measure for total revenue by material description. Original measure below:
IF(HASONEVALUE((PROD_PRT[MaterialDescription])),CALCULATE([Total Revenue], FILTER(PROD_PRT, RANKX(ALL(PROD_PRT), [Total Revenue])<=25)), blank())
I’ve played with this a number of ways, but I cannot get a measure that takes into account the slicers included in the pivot table. When I apply the slicers from the PROD_PRT table, the result does not include all 25 responses. I think it has something to due with ALL(PROD_PRT) but I have tried many variations and cannot figure it out. Your help will be greatly appreciated.February 4, 2016 at 6:22 pm #3273
Your intuition is correct, the surprise is coming from ALL(PROD_PRT). Because ALL has removed all filters before ranking, you are ranking against the entire data set and then your slicers are thinning out the results that passed the filter criteria.
TomFebruary 8, 2016 at 1:24 pm #3289
- Started: 0
- Replies: 2
- Total: 2
Thanks Tom, I appreciate the response. How do I get the top 25 products after the slicers have thinned out the results?
You must be logged in to reply to this topic.