topic Re: Exam Item Analysis in DAX Commands and Tips

Exam Item Analysis

rquizon09 — Fri, 11 Oct 2024 08:07:28 GMT

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

Re: Exam Item Analysis

bhanu_gautam — Fri, 11 Oct 2024 08:41:51 GMT

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.

Re: Exam Item Analysis

rquizon09 — Fri, 11 Oct 2024 09:09:37 GMT

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

Re: Exam Item Analysis

bhanu_gautam — Fri, 11 Oct 2024 09:13:02 GMT

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

Re: Exam Item Analysis

rquizon09 — Fri, 11 Oct 2024 09:33:52 GMT

Hi bhanu_gautam

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

Re: Exam Item Analysis

rquizon09 — Fri, 11 Oct 2024 15:55:18 GMT

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

Re: Exam Item Analysis

rquizon09 — Mon, 14 Oct 2024 01:05:04 GMT

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

Re: Exam Item Analysis

Anonymous — Tue, 15 Oct 2024 05:23:57 GMT

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.

Re: Exam Item Analysis

rquizon09 — Tue, 15 Oct 2024 06:57:15 GMT

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