Exclude Top

[CEllis]

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.

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[amitchandak]

@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]

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.

[CEllis · Accepted solution]

I ended up with this tutorial in then https://www.youtube.com/watch?v=WJ0p638zpwA

Worked well