Forum Discussion
Exam Item Analysis
Hi, Newbie here. How can I sum the Item No.1 based on the Score Column. But before to get the SUM of Item No. 1, I need to Sort Descending order the Score Column because I need only those top N ...
bhanu_gautam
Super User
Super Userrquizon09 , You can use the RANKX function along with the EARLIER function to achieve this.
Rank =
RANKX(
FILTER(
'YourTable',
'YourTable'[Subject] = EARLIER('YourTable'[Subject])
),
'YourTable'[Score],
,
DESC,
DENSE
)
Then
You can create measures to calculate the sum of the top N and bottom N scores.
TopN_Sum =
CALCULATE(
SUM('YourTable'[Item No.1]),
FILTER(
'YourTable',
'YourTable'[Rank] <= N // Replace N with the number of top scores you want
)
)
BottomN_Sum =
CALCULATE(
SUM('YourTable'[Item No.1]),
FILTER(
'YourTable',
'YourTable'[Rank] > (MAXX(ALL('YourTable'), 'YourTable'[Rank]) - N) // Replace N with the number of bottom scores you want
)
)