Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi,
Share some data, explain the question and show the expected result.
@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
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.