cancel
Showing results for
Did you mean:
Frequent Visitor

## Aggregating table and summing only the top 3 values

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

## Re: 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! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

4 REPLIES 4
Super User I

## Re: 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! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Microsoft

## Re: Aggregating table and summing only the top 3 values

Hi @vemchance ,

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)
)

Best Regards,

Liang

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

Frequent Visitor

## Re: Aggregating table and summing only the top 3 values

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

Super User I

## Re: Aggregating table and summing only the top 3 values

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 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors