Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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!

Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

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

4 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        JarroVGIT
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago
        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 🙂
  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.