cancel
Showing results for
Did you mean:
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
Community Champion

@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
Microsoft

Hi @Anonymous,

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

Regards

11 REPLIES 11
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

Community Champion

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?

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.

Community Champion

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.

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?

Community Champion

@Anonymous the formula @Vvelarde gave should work for that.

Proud to be a Super User!

Anonymous
Not applicable

Look

The SumTOPNa expression is:

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

Community Champion

@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
Anonymous
Not applicable

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

Microsoft

Hi @Anonymous,

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

Regards

Announcements

#### Check it out!

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

#### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors