Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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?
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
@v-shex-msft No problem. I have added some sample data in my post with a link to download an excel file.
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
@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
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |