Home Forums Power Pivot Looking for help with Ranking comparisons over time

Tagged: 

This topic contains 12 replies, has 2 voices, and was last updated by  tomallan 12 hours, 36 minutes ago.

Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Posts
  • #8419

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    I have a table with a bunch of people’s hourly earnings.

    I have created a ranking of the max rate that people have for the month, and also for the prior month.

    I would like to create a ranking of the individual across all prior months.

    I have the following metrics:

    Max Rate = Max(EmpTrans[HourlyRate])
    Rate Rank = RANKX(ALL(EmpTrans[Emp#],EmpTrans[FullName]),[Max Rate])
    Prev Month Rank = CALCULATE([Rate_Rank],
    PREVIOUSMONTH(‘Calendar'[Date]))

    But I realized what I want is the persons all time rank excluding the current Month. Or perhaps just the max rate of all months previous to this month.

    Help please.

    #8421

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    What is the name of the column used in EmpTrans that relates to the Date column in the Calendar table?

    And what are you using to analyze your data? Power BI or Excel Power Pivot? If Excel, what version: 2010, 2013 or 2016?

    Can you put up some sample data that where you know the correct answers? Data does not have to be real data, but the structure of your tables should be consistent with the information provided so far:

    Calendar table, with (at least) a column for Date (does your calendar table have a column indicating the date belongs to the current month? if so, please include it because it will most likely be part of a formula).

    EmpTrans table, with columns for Hourly Rate, Emp#, Full Name, and what-ever-the-date-column-is-named.

    Looking forward to your response.

    Tom

    #8426

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    Power Pivot on Excel Office 365 Pro Plus (2016 – 64 Bit)?

    Tables:

    Calendar : this is dynamically built of the [Pay Period End] dates in the EMPTrans Table
    Date | Year-Month | Year | Month | Day ….
    … Starts with earliest Pay Period End Date in the trans table
    09/01/2017 | 2017-09 | 2017 | 09 | 01
    09/02/2017 | 2017-09 | 2017 | 09 | 02

    10/01/2017 | 2017-10 | 2017 | 10 | 01

    EMPTrans:
    EMP# | Fullname | RateCode | Rate | Hours | Gross | Pay Period End |

    1234  | Gerry Geyser | ST | 29     | 30 | 957.00 | 07/07/2017

    1234  | Gerry Geyser | ST | 25     | 40 | 1320.00 | 08/25/2017
    1234  | Gerry Geyser | ST | 25     | 22 | 550.00 | 09/01/2017
    1234  | Gerry Geyser | OT | 37.5 | 10 | 375.00 | 09/01/2017
    1234  | Gerry Geyser | ST | 25     | 20 | 500.00 | 09/08/2017

    2345  | Bobby Bones | ST | 22     | 35 | 770.00 | 06/23/2017

    2345  | Bobby Bones | ST | 22     | 35 | 770.00 | 08/25/2017
    2345  | Bobby Bones| ST | 30     | 36 |1080.00 | 09/01/2017
    2345  | Bobby Bones | OT | 45    | 8   | 360.00 | 09/01/2017
    2345  | Bobby Bones | ST | 30     | 20 | 600.00 | 09/08/2017

    In September Gerry’s best Straight rate (ST) was 25$  ( Ranks 2nd)
    In August it was also 25$  ( Ranks 2nd)
    Previously his best rate previously was 29$ (Ranks 1st)

    In September Bobby’s best Straight rate (ST) was 30$ (Ranks 1st)
    In August it was 22$  ( Ranks 2nd)
    Previously his best rate previously was also 22$ (Ranks 2nd)

    On a pivot table I have put Max rate, Rate Rank, the previous month rank and fingers crossed a Previous All time rank.
    Using slicers I have selected just:  September and Straight time (ST)

    EMP# |  Max Rate  |  Rate Rank |  Previous Month Rank | *Previous All Time Rank

    1234    |        25      |     2     |    2   |    *1
    2345   |       30       |    1    |    2    |   *2

    * this is the measure I don’t know how to define.
    Hopefully this all made sense.
    I would like to know the rank of the persons highest rate defined prior to the current month. I wish to identify people that made significant jumps and I would like more than just the last month rank.
    Any attempts are greatly appreciated so thanks in advance.

    John

    [Edit Grammer and a couple rates to make it clear I don’t want the Previous all time rank to consider the current month.]

    #8427

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    uhg   1 edit and I spell Grammar wrong.

    #8428

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    John,

    Have just downloaded data and imported into an Excel 2016 workbook.

    Don’t worry about the spelling, it’s the numbers and ideas that are important here.

    Will get back to you shortly.

    Tom

    #8429

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    I noticed I made a mistake in the results description of Gerry’s Previous month rank, it should be 1st as 25 is larger than 22. and certainly in this case they both shouldn’t be 2nd.

    #8432

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    John,

    Because there is not an Employees table, I resorted to a trick: first creating a calculated column for the max rate of all time for each employee (excluding the “current” month). Also created a calculated column for the rank of max rate of all time (excluding “current” month). The values repeat, but it is always the same result for the same employee.

    The actual measures were then created using a MAX with the calculated columns (although I could have chosen MIN or AVERAGE and the result for the line-items rows would have been the same).

    It would have been easier with a table for employees.

    Tom

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

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    Thanks Tom,
    I have been trying to implement your version slightly different and am striking out. I need the Max Rate excluding “current” to be dynamic to the current month selected so I think the calculated column is sinking me.

    I will keep trying. I’ve attached an updated version of yours with a bit more data to illustrate. ie if you select July Bobby should get top spot in the prior months.

    I also added the employees table based on your comment.

    Kudos, to Tom for his effort and any others that might be up to the challenge.

    #8456

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    Attachment

     

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

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    See attached.

    Require Employee table columns to be on pivot rows.

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

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    Thank you so much Tom, you’re a Rockstar!

    In one iteration I had gotten as far as the Month start column but threw it away because I couldn’t figure out how to wedge it into the calculate filter and I thought I was going down the wrong path.
    When I saw the MIN function (in dynamic month start) I almost thought you had gone down the disconnected slicer path.

    Finally the light bulb went off. This is so beautiful and is way more functional as it will adjust to other time frames ie quarters or years.

    I don’t see Kudos point’s in this forum but you earned it in my book.

    Thanks again.

    #8471

    dircur
    Participant
    • Started: 6
    • Replies: 13
    • Total: 19

    I setup my calendar columns in the power Query import, and when I tried to use the Date.EndOfMonth Function with the optional , -1 for the previous month it errored.  Because I also had a day field I used,  “=Date.AddDays([Date], -1*[Day]+1)” for my start of month calculation.

    #8475

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    I believe the Power Query Date.EndOfMonth and Date.StartOfMonth do not take a second value, such as -1, but the DAX function EOMONTH does.

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

You must be logged in to reply to this topic.