Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 🙂
Solved! Go to Solution.
@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] ) ) ) )
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
@Anonymous
hi, you can use TOPN
SumTOPN = CALCULATE ( SUM ( Table2[Value] ), TOPN ( 10, Table2, Table2[Value] )
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?
Proud to be a Super User!
@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.
Proud to be a Super User!
@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.
Proud to be a Super User!
Look
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] ) ) ) )
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.
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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |