Forum Discussion

rush's avatar
rush
Helper V
7 years ago
Solved

Ranking with Movement

Hi All

 

I would like to rank my Total Revenue by Client based on quarters and comparing Q3 (Current Rank) vs Q2 (Previous Rank) with the movement next to it along with an icon (Up, Down & Neutral sign). 

 

Your help is much appreciated.

An example can be seen below:


 

Sample data that has been aggregated: Excel Data

  • rush's avatar
    rush
    7 years ago

    AnonymousThanks. I will try your option out and see if it will work out.

     

    Vvelarde& Anonymous I combined both your solutions to get what I needed which was a static date reference comparing 2 different date ranges.

    Rank Change = 
    
    VAR Previous_Rank = 
    CALCULATE ( RANKX (
        ALL ( 'Profitability Ranking Overview'[Client Switch for Ranking] ) , [Total Rev] ) ,
        KEEPFILTERS('Profitability Ranking Overview'[FY] = 2017) ,
        KEEPFILTERS('Profitability Ranking Overview'[FQ No.] <= 2) )
    
    Var Current_Rank = CALCULATE(RANKX(
        ALL ( 'Profitability Ranking Overview'[Client Switch for Ranking]), [Total Rev]) ,
        KEEPFILTERS ( 'Profitability Ranking Overview'[FY] = 2018 ) )
    
        VAR Rank_Condition = 
        SWITCH (
            TRUE () ,
            [Change in Rank]  > 0 , "+" & [Change in Rank] ,
            [Change in Rank]  = 0 , "-" ,
             [Change in Rank] )
    
            Return
    
             Rank_Condition
    
          

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi rush,

     

    Can you please share some sample data for test? It is hard to test and coding formula without any sample data.

     

    Regards,

    Xiaoxin Sheng

    • rush's avatar
      rush
      Helper V

      Anonymous No problem. I have added some sample data in my post with a link to download an excel file.

      • Anonymous's avatar
        Anonymous
        Not applicable

        I decided to give this a shot because it's something that is a little new to me.  

         

        First thing I did was take your data and add to Power Query to clean it up and make it usuable ( at least how I was going to use it).  Nothing to intense, just some renaming, splitting, etc.  I needed to get the FQ by itself, as use that (FQ) and FY to create a unique FyFQID

         

        FyFqID = 

        [FY]*100+[FQ]

        Did that in PQ and loaded that clean table to the data model:

         

        Then I created an Index column that will be used to figure out what was previous.  

        Index = 
        VAR CurrentClient ='Rank Exampl'[Client] 
        VAR CurrentFQ= 'Rank Exampl'[FyFqID]
        RETURN
        
        CALCULATE(
            COUNTROWS(
                FILTER(
                    ALL( 'Rank Exampl'),
                        'Rank Exampl'[Client]=CurrentClient
                            && 'Rank Exampl'[FyFqID] >= CurrentFQ
                )
            )
        )

         

        So now we have data that we can use, time to build some measures.  

         

        1. base measure:

        Total Rev = SUM ( 'Rank Example'[Revenue])

        2.  Meaure for current Rank:

        Current Rank = 
        RANKX(
            ALL ( 'Rank Example'[Client]),
            [Total Rev]
        )

        3.  Measure for Previous Rank (which is why  we needed an "Index" column):

        Previous Rank = 
        RANKX(
            ALL('Rank Example'[Client]), 
            CALCULATE(
                [Total Rev], 
                FILTER( 
                    ALLEXCEPT('Rank Example','Rank Example'[Client]), 
                        MAX('Rank Example'[Index])-1 = 'Rank Example'[Index]
                    )
                )
            )

        4.  Change in Rank is just subtracting the Prev and Current:

        Change in Rank = [Previous Rank] - [Current Rank]

         

        It's not done as you wanted some more formatting, but the heart of it is there i believe.  There also needs to be some logic added on the 1st period, since there's nothing to compare it to.  But ran out of time 

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    rush

     

    Hi, you can try with this:

     

    1. Add a column with the start of quarter

     

    Q12018 = 01/01/2018 

    Q22018 = 01/04/2018

     

    2. Add a calendar

     

    3. Add a column with the Quarter

     

    4. Related both tables with the dates

     

    5. Insert a Slicer with Quarters from Calendar Table

     

    6. Create 3 measures:

     

    RankthisQuarter =
    RANKX ( ALL ( Table1[Client] ), CALCULATE ( SUM ( Table1[Revenue] ) ) )
    
    RankPreviousQuarter =
    CALCULATE ( [RankthisQuarter], PREVIOUSQUARTER ( MyCalendar[Date] ) )
    
    Variation =
    VAR _VariationNumber = [RankthisQuarter] - [RankPreviousQuarter]
    RETURN
        SWITCH (
            TRUE (),
            _VariationNumber > 0, UNICHAR ( 9650 ) & "+"
                & _VariationNumber,
            _VariationNumber = 0, BLANK (),
            UNICHAR ( 9660 ) & _VariationNumber
        )
    

    7. Insert a table visual.

     

    8. Ready

     

    Regards

     

    Victor

     

    • rush's avatar
      rush
      Helper V

      VvelardeThank you but I have an issue trying to create a start of quarter from my fiscal year period in my Date Table which is:

       

      Starting March -  Ending Feb.

      How would I create a calculated column to show that?