How to get top5 values based on a field.

[atpostdata]

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:

ID	values	result
1	-6	negative
2	-5	negative
3	-4	negative
4	-3	negative
5	-2	negative
6	-2	negative
7	-1	negative
8	-1	negative
9	1.75	positive
10	1.75	positive
11	1.777	positive
12	1.8162	positive
13	1.8832	positive
14	1.8908	positive
15	1.892	positive
16	1.9348	positive
17	1.9348	positive
18	2.1009	positive
19	2.5443	positive
20	3.0544	positive
21	0	neutral
22	0	neutral
23	0.1	neutral
24	0.2	neutral

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

ID	values	result
1	-6	negative
2	-5	negative
3	-4	negative
4	-3	negative
5	-2	negative
6	-2	negative
14	1.892	positive
15	1.9348	positive
16	1.9348	positive
17	2.1009	positive
18	2.5443	positive
19	3.0544	positive

@rubayatyasmin@Vijay_A_Verma

[msahari]

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]

pls try this

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

[atpostdata]

Hi,
File is blank.

[Ahmedx · Accepted solution]

[atpostdata]

for value .99 it gives diiferent ranking

[Ahmedx]

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

[atpostdata]

yes,  I did but no luck.

[Ahmedx]

show what the result should be

[atpostdata]

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

[Ahmedx]

can't be.
check again

[atpostdata]

now file is not available. Can you upload it

[Ahmedx]

plse try

https://dropmefiles.com/39DAq

[atpostdata]

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.

[rubayatyasmin]

@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?

[atpostdata]

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]

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

[Ahmedx]

Share sample pbix file to help you.

[atpostdata]

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