Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Problem with RankX and YoY measures

Hi All, 

I'm wondering if someone can help me.... 

 

I have a large data set, but its market intelligence data, so it only has Quarter as the date column, so firstly I have had to make a Many:Many bi-directional link to the date table. 

 

Example fact Table here 

 

My problem is when I want to create a table to show the top 5 companies, plus 'Others' for the rest and then add YoY measures.  

This is what I've done so far..  

 

1. I created a Summary Table of the fact table: 

 

 

Summary = SUMMARIZECOLUMNS('Full Data Table'[Company], 'Full Data Table'[Quarter] , 'Full Data Table'[Lenovo_Segment],"Units",sum('Full Data Table'[Units]))

 

 

 

2. I created a new column in the summary table and used RankX to rank by Company 

 

 

Rank by units = Rankx(CALCULATETABLE(Summary,ALLEXCEPT(Summary,Summary[Lenovo_Segment], Summary[Quarter])),Summary[Units])

 

 

 

3. I added a column with an if Statment to list the top 5 companies and make all remaining compnies 'Others' 

 

 

Top 5 = IF (Summary[Rank by units] <= 6 && Summary[Company] <> "Others",Summary[Company], "Others")

 

 

 

 

Summary table view

 

I have then created a table as below.  

Notice 'Units Ths Year' works perfectly  

But 'Units last year does not as the ranking has changed and Dell from previous year is added when its ourside of this years ranking.  

 

 

Becuase i want to include'others' for comapnies outside the top 5 I believe I need to be able to calculate the units for last year baesed on the company rank for this year. I cannot work out how to do this.  

 

Can anyone please help?  

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , You can create a date from Qtr and year. seprate out Year and qtr and follow my video : https://youtu.be/yPQ9UV37LOU

     

    or create a new qtr year table (date) with new columns for qtr No and Year using left and right functions

     

    Qtr Rank = RANKX(all('Date'),'Date'[Qtr year],,ASC,Dense)
    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

     


    This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Np] <=max([Qtr no])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      @amitchauhan

      Thanks for your reply. You have missed my reason fro using the Summary table - this was so I can show the top 5 company and then place all remaining companies under the name 'Others'. This works for current Qtr, but does not work when i want yo do YOY calculations.

      It is the method of creating the 'Others' row which is giving me issues when I want the YOY calculation.

      Any idea how I could keep 'Others' and have YOY ?