Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Sum of TOPN Columns

Hey everyone, How do i SUM only the Top 10 values of a table? I tried this:   Total SUM TOPN = CALCULATE([Total SUM], FILTER(Table, RANKX(ALL(Table[Name]), [Total SUM],,DESC) <= 10))   but it d...
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Anonymous

     

    You need  to group the categories to applied the aggregation.

     

    One way is this:

     

     

    Top2RL =
CALCULATE (
    SUM ( Table3[Value] ),
    TOPN (
        3,
        GROUPBY ( Table3, Table3[Category] ),
        CALCULATE ( SUM ( Table3[Value] ) )
    )
)
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    Great to hear the problem got resolved.:smileyhappy: Could you accept the corresponding reply as solution to help others who has similar issue easily find the answer and close this thread?

     

    Regards

Anonymous's avatar
Anonymous
Not applicable
9 years ago

I tried with TOPN also but this does not work :/ any idea why?

I read that TOPN does not guarantee correct sorting

Anonymous's avatar
Anonymous
Not applicable
9 years ago

TOPN doesn't guarantee any particular sort order to the results, but the results are the correct top N results. So summing them up should work fine; 1 + 2 + 3 + 4 is the same as 1 + 3 + 4 + 2. What is wrong with the results you're getting? Can you give a sample that doesn't give the correct sum?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Anonymous  It works but the thing I want is, the TOPN sum to be grouped by Column1. How can I do that? This does not group by Column1 it only returns the sum of the first 10 rows.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Are you saying you want to see the top n rows with a sum for each row? If that's all, the latest desktop release makes it super easy; you just need a regular sum formula, then put a top n filter on the visual.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Anonymous no, I want the SUM of the TOP N rows as a measure.

        For example if I have this table and I want the sum od TOP 3:

         

        A 15     

        B 20

        C 30

        D 10  ====> A 35

        A 20                B 30
        B 10                C 55
        C 25                D 10

         

        I would get 35+30+55=120

         

         

        Do you know how can I do that?