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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
atpostdata
Frequent Visitor

How to get top5 values based on a field.

To retrieve the top 5 values from each category based on a specific field in Power BI,  I used below measure but it giving me all negative records.

 

 

 

 

 

Top5Negative Records = 
CALCULATE(
    [sum_of_values],
    FILTER(
        ALL(data[overall_feedback]),
        data[overall_feedback] = "negative"
    ),
    TOPN(5, data, data[compound], DESC)
)

 

 

 

 

 

smaple data:

 

IDvaluesresult
1-6negative
2-5negative
3-4negative
4-3negative
5-2negative
6-2negative
7-1negative
8-1negative
91.75positive
101.75positive
111.777positive
121.8162positive
131.8832positive
141.8908positive
151.892positive
161.9348positive
171.9348positive
182.1009positive
192.5443positive
203.0544positive
210neutral 
220neutral 
230.1neutral 
240.2neutral 


 expected output: The desired output is to show all tied values when calculating the top 5 records based on a  values field.

 

IDvaluesresult
1-6negative
2-5negative
3-4negative
4-3negative
5-2negative
6-2negative
141.892positive
151.9348positive
161.9348positive
172.1009positive
182.5443positive
193.0544positive

 

@rubayatyasmin@Vijay_A_Verma

1 ACCEPTED SOLUTION

18 REPLIES 18
msahari
Frequent Visitor

Let's work on getting the desired output where all tied values are included when calculating the top 5 records based on the values field.

Here's how you can achieve this in M Query:

  1. Load Your Data Source:

    • Open Power Query and load your data source.
  2. Sort the Table:

    • Sort the table by the values column in descending order.
  3. Identify the Top 5 Values:

    • Identify the unique top 5 values.
  4. Filter the Table:

    • Filter the table to include all rows where the values column matches any of the top 5 values.

Here's a sample M Query script to achieve this:

let
    // Step 1: Load your data source
    Source = Excel.Workbook(File.Contents("YourFilePath.xlsx"), null, true),
    Data = Source{[Name="YourSheetName"]}[Data],

    // Step 2: Sort the table by the "values" column in descending order
    SortedTable = Table.Sort(Data, {{"values", Order.Descending}}),

    // Step 3: Identify the unique top 5 values
    Top5Values = List.FirstN(List.Distinct(List.Sort(Table.Column(SortedTable, "values"), Order.Descending)), 5),

    // Step 4: Filter the table to include all rows with the top 5 values
    FilteredTable = Table.SelectRows(SortedTable, each List.Contains(Top5Values, [values]))
in
    FilteredTable

Replace "YourFilePath.xlsx" and "YourSheetName" with your actual file path and sheet name. This script will sort the table by the values column, identify the unique top 5 values, and filter the table to include all rows with those values.

Ahmedx
Super User
Super User

Hi,
File is blank.

Screenshot_2.pngScreenshot_4.png

for value .99 it gives diiferent ranking

what should be?
did you try to change DESC to ASC

yes,  I did but no luck.

show what the result should be

I did round(values,2) and its working.

can't be.
check again

now file is not available. Can you upload it

atpostdata
Frequent Visitor

getting this error while creating measure A single value for column 'values' in table 'data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

@atpostdata Hey, I gave you the exact solution you asked for. I wrote the measures also. And attached a screenshot of the result. Did you follow my steps? Do you need pbix?


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Hi ,
I followed the same steps, its working for negative overall_feedback value but not for postive as postive values rank is comming greater than 5. 
overall_feedback contains three values postive, negative and neutral.
need top 5 records from each category based on values field.

rubayatyasmin
Super User
Super User

Hi, @atpostdata thanks for reaching out. 

 

I have tried with your demo data and achieved the result. The way I did it is by creating 3 calculated columns. 

1. Rank ASC column

2. Rank DSC column

3. to check whether or not these columns fall under top5

 

then used a visual level filter where the third columns value is true

 

rubayatyasmin_0-1689957346619.png

 

sample code for 3 calculated column 

1. 

Rank Asc =
    RANKX(
        ALL(Data),
        Data[values],
        ,
        ASC,
        Skip
    )
 
2. 
Rank Desc =
    RANKX(
        ALL(Data),
        Data[values],
        ,
        DESC,
        Skip
    )
 
3. 
IsInTopBottom5 = IF(Data[Rank Asc] <= 5 || Data[Rank Desc] <= 5, "Yes", "No")
 
table will look something like
 
rubayatyasmin_1-1689957496019.png

 

 
then apply a visual level filter where IsInTopBottom5 is yes
 
rubayatyasmin_0-1689517080227.png

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Ahmedx
Super User
Super User

Share sample pbix file to help you.

@Ahmedx @rubayatyasmin : sharing the sample data and .pbix file on below link.
powerbi 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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