Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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! 🙂
Proud to be a Super User!
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.
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.
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! 🙂
Proud to be a Super User!
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!)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |