Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Issues with TOP N not working.

Hello,

 

Quick bit of context. Building a table to show the past 13 weeks worth of policy sales for financial advisers. I have a column for Unique ID (UID), name, the company they work for and then the proceeding 13 columns have the total policies for the current and previous 12 weeks. 

I need to create a top 10 version of this table to show the top 10 Advisers for the current week. I know I can do thiss using the top N function and filter on the Adviser UID. I have tried to change the UID filter on the visual to top N but it is not giving me the option to apply the filter. 

Further more the videos/guides/help in this forum I can find show the filter section looking completely different to what I can see, showing the ability to choose how many of the top N to show and a different lay out. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hello, thanks fo ryour rpely but I actualy found that I can type the Top N number next to where it says "Top N" in my screen grab. Turns out that there is not box outline so you wouldn't know its there, it was through pur dumb luck that I found it. 

    Might be something for Power BI to look at in the future. 

2 Replies

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

    Anonymous , First create a This week measures

     

    example

     

    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    where Rank

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    Then You TOPN

    https://youtu.be/QIVEFp-QiOk

     

    example

    CALCULATE(concatenatex(Table, Table[ID]) ,TOPN(10,allselected(Table2[Reason Name]),calculate(Count(Table1[ID])),DESC),VALUES(Table2[Reason Name]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello, thanks fo ryour rpely but I actualy found that I can type the Top N number next to where it says "Top N" in my screen grab. Turns out that there is not box outline so you wouldn't know its there, it was through pur dumb luck that I found it. 

      Might be something for Power BI to look at in the future.