Exam Item Analysis

[rquizon09]

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 and bottom N of score to SUM the value of Item No.1

I have example here 

Thank you

[rquizon09]

Hi @Anonymous 
The Unique Ranking is already solved.
I just want to clarify this formula 

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
)
)

Because this formula does not functioning.

I already have Unique Ranking of Row but I want is to Sum the TopN and BottomN based on Ranking Column.

Thank you

[rquizon09]

Hi @bhanu_gautam 
I already get the unique ranking every row.
I want to SUM now the Item No.1 based on the Ranking. but the problem is the ranking is equal to 27% of the total count rows per subject. Meaning, the count or ranking will be dynamic.

Thank you

[Anonymous]

Hi @rquizon09 

Thanks for the reply from bhanu_gautam .

rquizon09 , if your problem is still not solved, could you please explain in more detail what you mean by "but the problem is the ranking is equal to 27% of the total count rows per subject."? What does total count refer to? This way we can better help you. Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rquizon09]

Hi @bhanu_gautam 
I want to get the unique rank per subject even though the student scores are duplicated

[rquizon09]

Hi @bhanu_gautam 

Regarding on the calculated column, the formula is not functioning or Am I not execute it properly?

[bhanu_gautam]

@rquizon09 , 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
)
)

[rquizon09]

Hi @bhanu_gautam 

Thank you for your feedback.
I have question on calculated column, I want to rank them based on the subject.

For example.

Subject 1 Rank 1

Subject 1 Rank 2

Subject 1 Rank 3
Subject 2 Rank 1

Subject 2 Rank 2
Subject 3 Rank 1

etc.

Thank you

[bhanu_gautam]

@rquizon09 , Try using below steps

First, sort your data by the Score column in descending order.

Create a calculated column to rank the scores. You can use the RANKX function for this.
Rank = RANKX(ALL('YourTable'), 'YourTable'[Score], , DESC, DENSE)

Filter Top N and Bottom N:

TopN_Sum =
CALCULATE(
SUM('YourTable'[Item No.1]),
FILTER(
'YourTable',
'YourTable'[Rank] <= N // Replace N with the number of top scores you want
)
)

Combine the Measures:
Create a final measure to sum the values from the top N and bottom N.
Total_Sum = [TopN_Sum] + [BottomN_Sum]
Add a card or any other visual to display the Total_Sum measure.