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", ...
Need Help
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.