cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vemchance
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 IDScore
Jeff100
Jeff200
Jeff300
Rob100
Rob100
Alan100
Alan100
Alan100
Alan100
Jenny100
Jenny200
Jenny100
Richard100
Richard200
Richard200
Richard300

 

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 IDScore
Jeff600
Rob200
Alan300
Jenny400
Richard700

 

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

image.png

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:

image.png

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


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User I
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):

image.png

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:

image.png

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


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

Microsoft
Microsoft

Re: Aggregating table and summing only the top 3 values

Hi @vemchance ,

 

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

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.

vemchance
Frequent Visitor

Re: Aggregating table and summing only the top 3 values

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

Super User I
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 🙂

Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors