Forum Discussion

rquizon09's avatar
rquizon09
Frequent Visitor
1 year ago

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

 

I have example here 

 

Thank you

 

8 Replies

  • 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.

  • rquizon09's avatar
    rquizon09
    Frequent Visitor

    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

  • 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's avatar
    rquizon09
    Frequent Visitor

    Hi bhanu_gautam 

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

     

  • rquizon09's avatar
    rquizon09
    Frequent Visitor

    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's avatar
      Anonymous
      Not applicable

      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's avatar
    rquizon09
    Frequent Visitor

    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