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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Power BI TOP N

Hello, Everyone

Is it possible to find TOP 10,20,30, etc,.? I want to show like this image below and can filter by date too.

Thank you in advance for any solution.

CaptureBI.PNG

1 ACCEPTED SOLUTION

Hi , @Anonymous 

Here is a demo.

pbix attached

 

You need to create a calculate column first:

Row Number = COUNTROWS(FILTER('Table','Table'[Amount Dollar]>=EARLIER('Table'[Amount Dollar])))

Enter Table as below:

21.png

Then create the measure "pecentage by amount" as below  :

Measure =
VAR _topn =
    SELECTEDVALUE ( 'Table 2'[TOP N] )
VAR tab =
    TOPN ( _topn, 'Table', 'Table'[Amount Dollar] )
RETURN
    DIVIDE (
        SUMX ( tab, 'Table'[Amount Dollar] ),
        CALCULATE ( SUM ( 'Table'[Amount Dollar] ), ALL ( 'Table' ) )
    )

The result will show as below:

22.png

 

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-easonf-msft
Community Support
Community Support

Hi , @Anonymous 

Not very clear. Can you explain bit more.

Can you tell me how to  calculate "Percentage By Amount"  and what is  Top N sorted by?
Sample data(excel /pbix file)  will make it easier for us to understand  your excepted result  .

 

 

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

Dear @v-easonf-msft thank you for ask this question.sorry for my question not clear.

this is my sample data i created. Please kindly check it.

my method that i tried:

1. i create measure for sum amount.

2. i create rank measure

 

https://jia666-my.sharepoint.com/:x:/g/personal/khihortmrm_xkx_me/Ed_SHMrOvjFJiyBWhXMObRkB2bu9MmT0Ls... 

thank you in advance.

Hi , @Anonymous 

Here is a demo.

pbix attached

 

You need to create a calculate column first:

Row Number = COUNTROWS(FILTER('Table','Table'[Amount Dollar]>=EARLIER('Table'[Amount Dollar])))

Enter Table as below:

21.png

Then create the measure "pecentage by amount" as below  :

Measure =
VAR _topn =
    SELECTEDVALUE ( 'Table 2'[TOP N] )
VAR tab =
    TOPN ( _topn, 'Table', 'Table'[Amount Dollar] )
RETURN
    DIVIDE (
        SUMX ( tab, 'Table'[Amount Dollar] ),
        CALCULATE ( SUM ( 'Table'[Amount Dollar] ), ALL ( 'Table' ) )
    )

The result will show as below:

22.png

 

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

wOw so cool. Thank you 😁 @v-easonf-msft  😁 that is what i really need.

amitchandak
Super User
Super User

Option 1 I can think of is Ranking and binning

https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

If there is no filter you can create Rank and rank group on the table

 

The second option I think of is creating a summarize table then Create a new Rank column and again group rank.

and use this table.

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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