Home Forums Power Pivot Ranking

Tagged: ,

This topic contains 10 replies, has 3 voices, and was last updated by  stafjoy 3 years, 4 months ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #1851

    jmckenzie
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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?

    Attachments:
    You must be logged in to view attached files.
    #1854

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Hi,

    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.

    #1856

    jmckenzie
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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.

    #1859

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Hi,

    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] ),
    IF (
    ISFILTERED ( Branch[RegionName] ),
    RANKX (
    ALL ( Branch[RegionName] ),
    [RmrByService – RMR Sum]
    )
    )
    )

    Attachments:
    You must be logged in to view attached files.
    #1864

    jmckenzie
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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?

    Attachments:
    You must be logged in to view attached files.
    #1866

    jmckenzie
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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.

    #1869

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Hi,

    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”).

    #1870

    jmckenzie
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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.

    Attachments:
    You must be logged in to view attached files.
    #3272

    stafjoy
    Participant
    • 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.

     

    #3273

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Hi,

    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.

    Tom

    #3289

    stafjoy
    Participant
    • 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?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.