Aggregating table and summing only the top 3 values

[Anonymous]

Hello,

I have a dataset which lists all the qualifications a person has on each row. I would like to aggregate these, so my table only shows one line per person, and sums the score of their combined qualifications. However, I only want their top 3 qualification scores in my new table. 

Here is an example dataset produced in Excel - unfortunately I can't share the full data because it's internal data:

Unique ID	Score
Jeff	100
Jeff	200
Jeff	300
Rob	100
Rob	100
Alan	100
Alan	100
Alan	100
Alan	100
Jenny	100
Jenny	200
Jenny	100
Richard	100
Richard	200
Richard	200
Richard	300

I would then like to create only one row per person. But instead of summing the 'score', I only want to sum the top 3 scores. So for example, the score '100' would be ignored for Richard in the new table as it is too low to make the top 3. Similarly, Alan has four scores of 100 - the final calculation would only be 300 for him, as we can only count 3 top values. Basically, the function is limited to only counting a maximum three scores no matter how many (or how few!) that person has. 

So my final table I would like to look something like this:

Unique ID	Score
Jeff	600
Rob	200
Alan	300
Jenny	400
Richard	700

Ideally I am trying to recreate this as a new table in the query editor that references the original table, as I need to repeat this process with new data over and over. However I am happy to look at solutions that use measures. I have tried various topn measures, grouping the data in the measure, but it either doesn't work and calculates everything or doesn't work at all.

Any help would be greatly appreciated!

[V-lianl-msft]

Hi @Anonymous ,

First, you can create an index column in edit query.

Then you need to rank the "score" column with "RANKX" function.

RANK =
RANKX (
    FILTER ( Sheet3, Sheet3[Unique ID] = EARLIER ( Sheet3[Unique ID] ) ),
    RANKX (
        FILTER ( Sheet3, EARLIER ( Sheet3[Score] ) <= Sheet3[Score] ),
        Sheet3[Index],
        ,
        DESC
    )
)

Finally, use the "TOPN" function to sum the three largest values of each group.

Top3_sum =

CALCULATE (

    SUM ( Sheet3[Score] ),

    TOPN (

        3,

        FILTER(Sheet3,EARLIER(Sheet3[Unique ID])=Sheet3[Unique ID]),

       Sheet3[RANK],DESC)

    )

Please refer to the pbix.

Best Regards,

Liang

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

[JarroVGIT · Accepted solution]

Well there are a lot of fun questions tonight in the community! 😄 I came up with a solution in DAX, not Power Query though. I think it still suits your needs.

Alright so I've recreated your table, but then I added a Index column in the Query editor. This is needed for having tie breaks (e.g. for Alan you only want 3 values, but the top 4 are the same so we need to tiebreak those). The index column will act like the secondary column to rank against if there is a tie. 

Now we are going to add a calculated column. This will provide a rank per user based on the score (primairly) and index (in case of ties). The DAX is: 

InternalRankingByName = 
VAR curID = Ranking[Unique ID]
RETURN
RANKX(FILTER(Ranking, Ranking[Unique ID] = curID), 
    RANKX(Ranking, Ranking[Score], , DESC, Skip) +
        DIVIDE(RANKX(Ranking, Ranking[Index]), COUNTROWS(Ranking)+1),
    , ASC, Dense)

Let me know if you want an explanation on what is happening here. I have to run soon so I'll just leave this as a working solution. This creates the following table (from Data view):

Now we can create a Calculated table based on this with the following dax:

Table_you_Want = SUMMARIZE(Ranking, Ranking[Unique ID], "Total top 3", SUMX(FILTER(Ranking, Ranking[InternalRankingByName] < 4), Ranking[Score]))

This results in the following table:

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

@JarroVGIT :

Yes! It worked perfectly. 

Absolutely brilliant, thank you for your hard work. I didn't think to use an index column and solve the tied ranking. If you do have a moment and wanted to go over the first step on what's happening I'd be happy to hear it - only for my own understanding, but I might be able to figure it out after using this a few more times!

I have a new table now with all the information I need and did a few spot checks, all the scores are correct. This has been plaguing me all week and I'm so happy to have a solution (in time for Christmas, too!)

[JarroVGIT]

Good to hear! Please make sure you mark it as the solution so others may find it as well 🙂 and send me a PM on your question, I'll be happy to walk you through step by step what is happening at what point in the DAX 🙂