Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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 🙂

2 ACCEPTED SOLUTIONS

@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] ) )
    )
)



Lima - Peru

View solution in original post

Hi @Anonymous,

 

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

 

Regards

View solution in original post

13 REPLIES 13
Vvelarde
Community Champion
Community Champion

@Anonymous

 

hi, you can use TOPN

 

SumTOPN =
CALCULATE ( SUM ( Table2[Value] ), TOPN ( 10, Table2, Table2[Value] ) 



Lima - Peru
Anonymous
Not applicable

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

I read that TOPN does not guarantee correct sorting

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@KHorseman  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.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@KHorseman 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 the formula @Vvelarde gave should work for that.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Look

Screenshot_6.png

The SumTOPNa expression is: 

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

@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] ) )
    )
)



Lima - Peru

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

You're amazing! Thank you for this answer.

Anonymous
Not applicable

THANKS @Vvelarde!!! I've been trying to figure this out whole day 😄 

Hi @Anonymous,

 

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

 

Regards

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.