How to get top5 values based on a field.

Share sample pbix file to help you.

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

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

 

 

 

then apply a visual level filter where IsInTopBottom5 is yes

 

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.

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RiWhMjhJMbp9W1FUB?e=DSe92H

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.