Measure for Sum of Top N items per category

[cesarowicz]

I have a table of student assessment data as follows:

I need to calculate the sum of each students top 2 Scores where Category = 'Core'

It needs to be a measure rather than a calculated column because the I need to filter and group by different combinations of Assessment.

My thinking is that for each row in the table I need to generate a nested calculated table of that student's 'Core' scores, apply a top N to it, and then return the score if the id of the current row matches either of those returned by the inner table. But I can't work out how to express this in a DAX formula.

I started with this formula, which returns the just the top 1 'Core' score per student, though it fails where there are the student has three matching 'Core' results and I can't see how to overcome this (or make it work for the top 2) without using a TOPN/CALCULATETABLE somewhere.

CALCULATE (
    SUM ( Results[Score] ),
    FILTER (
        ALLEXCEPT ( Results, Results[Assessment] ),
        SUMX (
            FILTER (
                Results,
                Results[Category] = "Core"
                    && Results[Category] = EARLIER ( Results[Category] )
                    && Results[Student] = EARLIER ( Results[Student] )
                    && (
                        ( Results[Score] < EARLIER ( Results[Score] ) )
                            || (
                                (
                                    Results[Score] = EARLIER ( Results[Score] )
                                        && Results[Result_ID] < EARLIER ( Results[Result_ID] )
                                )
                            )
                    )
            ),
            Results[Score]
        )
    )
)

Ideally for performance/size reasons I'd like to get rid of the Result Id column as well as I only added it to try and crack this formula.

Any ideas? 

[v-sihou-msft]

@cesarowicz

In this scenario, if you want to get the top 2 scores context without using TOPN, you need to use RANKX() to filter top 2 context.

You can create a rank measure like below:

Score Rank = RANKX (
    ALLEXCEPT ( Table, Table[Student], Table[Assessment], Table[Category] ),
    CALCULATE ( SUM ( Table[Score] ), FILTER ( Table, Table[Category] = "Core" ) ),
    ,
    DESC,
    DENSE
)

Then you can create a measure to calculate the top 2 scores based on above rank:

Top 2 Scores =
CALCULATE ( SUM ( Table[Score] ), FILTER ( Table, [Score Rank] <= 2 ) )

Regards,

[cesarowicz]

Hi Simon

Thanks for looking at this.

The Top 2 Scores measure just seems to be returning all the scores, however?

 

 Should I be inserting it into my original formula somewhere rather than using it as a standalone measure?

[cesarowicz]

OK, here's another attempt at trying to get my head round how to do this.

I've simplified things by just looking at the Result_ID so there's no need to do any additional ranking.

I can count the number or records with a lower Result_ID then the current iterated row of the outer FILTER, this formula just sums the total of those records. What I want to do is sum the score from the outer FILTER only where the count of the records in the inner FILTER is 2 or fewer.

CALCULATE (
    SUM ( Results[Score] ),
    FILTER (
        ALLEXCEPT ( Results, Results[Assessment], Results[Student] ),
        COUNTROWS (
            FILTER (
                Results,
                Results[Result_ID] > EARLIER ( Results[Result_ID] )
                    && Results[Category] = "Core"

I get that COUNTROWS in the formula above is just a placeholder but I really want to be able to say COUNTROWS[...] = 2 - there must be a way to do this somehow?

[larcaris]

@cesarowicz Did you resolve it? I have the same problem that you, and i be able to resolve using measure too.