Aggregating table and summing only the top 3 values

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! 🙂