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

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?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous the formula Vvelarde gave should work for that.

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    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] ) )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    THANKS Vvelarde!!! I've been trying to figure this out whole day :D 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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

    Look

    The SumTOPNa expression is: 

    SumTOPNa = CALCULATE(sum(Table1[Column2]), TOPN (3, PieChart, SUM(Table1[Column2]), DESC))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    You're amazing! Thank you for this answer.

  • singhranjeet101's avatar
    singhranjeet101
    Frequent Visitor
    3 years ago

    It works but when we try to filter values further. it returns blank table. How we can fix that?