cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesdj
Frequent Visitor

Help needed on coming up with TOP 5, 10 and 25 within one Visualization

Hello,

 

I'm looking for your help on coming up with a Matix or Table visualization that shows the sum of top 5, top 10 and top 15. I have the following Percentage column that has percentages sorted in descending order which I'm using to solve this issue. I tried doing it myselft but Power BI desktop keep saying that you only can use one TopN from the UI. So, I was thinking maybe there is a way to do it using DAX formula.

 

Data and Result.png

 

Thank you.

 

Here is the data that can be copied.

Percentage      
10.31%     
9.65%      
8.62%      
7.85%      
7.80%      
6.74%      
6.10%      
6%      
5.62%      
5.60%      
5.21%      
5.21%      
4.80%      
4.65%      
4.30%      
3.50%      
3.21%      
3.12%      
3.10%      
2.52%      
2.35%      
2.10%      
2%      
1.30%      
1.20%      
0.83%      
0.65%      
0.64%      
0.12%      
1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

Create a measure for each band as follows:

Top 5 = 
SUMX (
    CALCULATETABLE (
        TOPN ( 5, VALUES ( Table[Percentage] ), Table[Percentage], DESC )
    ),
    Table[Percentage]
)

 

Change 5 with the band size you want for each measure.

View solution in original post

2 REPLIES 2
Mohammad_Refaei
Solution Specialist
Solution Specialist

Create a measure for each band as follows:

Top 5 = 
SUMX (
    CALCULATETABLE (
        TOPN ( 5, VALUES ( Table[Percentage] ), Table[Percentage], DESC )
    ),
    Table[Percentage]
)

 

Change 5 with the band size you want for each measure.

View solution in original post

Hi Mohammad, Thank you so much for above DAX query. It's working perfectly for me.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.