Ranking with Movement

[rush]

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

[Vvelarde]

@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]

@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?

[v-shex-msft]

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]

@v-shex-msft No problem. I have added some sample data in my post with a link to download an excel file.

[Anonymous]

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 

[rush · Accepted solution]

@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