Forum Discussion

robertosangi's avatar
robertosangi
Helper I
4 years ago
Solved

Ranking values comparing two weeks

Hi, 

 

I have one question regarding the possibility to rank values based on a comparison between weeks.

 

Here the problem: I have this kind of table:


First of all i want to have first column as Wn and second column ad Wn-1, it is possible to do that? 
Thanks

Secondly I want to add two more columns. Each of them must rank (from 1 to n) the position of the highest value. In this example:
1. Palermo
2. Siracusa

....

Then, when I have the indexed value per each of the Wn and Wn-1 column I want to ad a sort of comparison between this index that shows if one unit performed better or worst respect the two last weeks.
In excel I cal use triangles up and down for indicate that. I want to buil somethink like this:

 

 

Thank you in advance

Need Help
  • robertosangi's avatar
    robertosangi
    4 years ago

    Seems is not working anyway johnt75 

     

    Those are now the formulas but the results seems wrong because i filtered the current week table manually and results 433 count and not 2264 as per measure 


    Could you please rewrite formulas as you expected? Thanks

     

13 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    Assuming that you have a date table, and assuming that you already have a measure which calculates PdL for the current week, then you can create additional measures as below

    PdL Prev Week = CALCULATE( [PdL], DATEADD( 'Date'[Date], -7, DAY) )

    Rank This Week = RANKX( 'Table', [PdL] )

    Rank Last Week = RANKX( 'Table', [PdL Prev Week] )

    Change in Rank = [Rank This Week] - [Rank Last Week]

    Once you've added all these to a table visual you can use conditional formatting on the Change in Rank column to add icons

    • robertosangi's avatar
      robertosangi
      Helper I
      4 years ago

      Hi johnt75 

       

      I didn't have a table for date. I have only a column from which I calculate weeks (WEEKNUM) as follow: 


      looking at this condition, how should i proceed?

      Thanks

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        The safest option would be to create a date table and link that to the date column in your data table.

        It would be possible to amend my code to work with a week number column, but it would be fiddly. Firstly you'd need to either change your current Week column so that it was only the week number, without the "Week " text, then you could change the measure to look at week - 1, but you'd run into problems when going over year boundaries when the week number resets. You'd also need to cater for situations where the week number could be 53, not 52.

        Safer and easier to create a date table.