Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cesarowicz
Frequent Visitor

Measure for Sum of Top N items per category

I have a table of student assessment data as follows:
 
image.png
 
 
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? 
4 REPLIES 4
v-sihou-msft
Employee
Employee

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

 

Hi Simon

 

Thanks for looking at this.

 

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

 2017-06-22_220541.jpg

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

 

 

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.