Forum Discussion

CEllis's avatar
CEllis
Resolver I
2 years ago
Solved

Exclude Top

Hi,

 

Is it posible to exclude the TopN figures.

 

I want to be able to filter out all values but the TopN ones.


To be able to do thisd but backwards

 

 

"EBACC A8",                       
    CALCULATE (
    SUM ( Assessment[Points] ),FILTER(Assessment,Assessment[Bucket]="EBACC"),
        TOPN (
               3,
        GROUPBY ( Assessment, Assessment[UPN (Pupil)], Assessment[YearGroup], Assessment[Collection], Assessment[Bucket] ),CALCULATE ( SUM ( Assessment[Points] ) ))))

 

 

 Any help greatly appreciated.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    CEllis , You can try window function there we can give start offset

     

    example measures

     

    M1= SUM ( Assessment[Points] )

     

    Top 1000 exclding Top 3 =

    CALCULATE([M1], WINDOW(4,ABS, 1000, ABS, ADDCOLUMNS(ALLSELECTED( Assessment[UPN (Pupil)], Assessment[YearGroup], Assessment[Collection], Assessment[Bucket] ),ORDERBY([M1],desc))))

     

    Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

     

     

    • CEllis's avatar
      CEllis
      Resolver I
      2 years ago

      I nearly get it
      I've added a measure 'Total Points' to only look at the "English" Bucket.

      I then added a column to the table below called [__English A8]

       

      Total Points = CALCULATE(SUM(Assessment[Points]),FILTER(Assessment,Assessment[Bucket]="English")  
)

__English A8 = CALCULATE([Total Points],KEEPFILTERS(WINDOW(1,ABS,1,ABS,SUMMARIZE(Assessment,Assessment[UPN (Pupil)],Assessment[YearGroup],Assessment[Collection],Assessment[Bucket]), ORDERBY(Assessment[Total Points],DESC))))

       

       

      What I was hoping for is the [__English A8] column would only show the first value because its the highest not show both values, by adding the 1,ABS,1 I thought that would restrict it to the first record.

       

      Many thanks for your help.