Forum Discussion
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
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
- AnonymousNot 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
- rushHelper V
Anonymous No problem. I have added some sample data in my post with a link to download an excel file.
- AnonymousNot 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
- VvelardeCommunity Champion
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