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 doesn't work. It gives me the total sum od all the rows.

Can someone tell me what am I doing wrong?

Thanks :)

  • 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

13 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Anonymous

     

    hi, you can use TOPN

     

    SumTOPN =
CALCULATE ( SUM ( Table2[Value] ), TOPN ( 10, Table2, Table2[Value] )
    • 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?