cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rush Member
Member

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:


Rank.PNG

 

Sample data that has been aggregated: Excel Data

1 ACCEPTED SOLUTION

Accepted Solutions
rush Member
Member

Re: Ranking with Movement

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

 

@Vvelarde& @Nick_M 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 6
Community Support Team
Community Support Team

Re: Ranking with Movement

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
rush Member
Member

Re: Ranking with Movement

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

Nick_M New Contributor
New Contributor

Re: Ranking with Movement

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:

PowerQ.png

 

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

Index Tab.png

 

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]

Final Table.png

 

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 

Super User
Super User

Re: Ranking with Movement

@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

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




rush Member
Member

Re: Ranking with Movement

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

rush Member
Member

Re: Ranking with Movement

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

 

@Vvelarde& @Nick_M 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