Top N and Bottom N in single chart

[Shailu198]

Hi,

I want to show Top N and bottm N using single chart in Power BI? Any help would be appreciated.

Regards,

Shailesh

[Anonymous]

Hi Shailesh,
Did this solution work for you? Can you please share a screenshot (masking the data except the numbers) in here?
Thanks,
Bhoga

[Anonymous · Accepted solution]

Hi, @Shailu198, 

You can use a simple power query to do that:

let
    SortedTable = Table.Sort(Persons, {"Age"}),
    Top_N = Table.FirstN(SortedTable, 3),
    Last_N = Table.LastN(SortedTable, 3),
    Combined_Table = Table.Combine({Top_N, Last_N})
in
    Combined_Table

First, sort the table by the column that you want. Then, take the Top_N and Last_N ( 3, in my example) and finally combine them.

[Shailu198]

Hi Caaarlos,

I don't have a direct column to sort the values, i need to do aggregration (like sum, count) and based on that it need to sort.

The method you mentioned is working fine for columns where we can sort direct. 

I tried like this - 

SortedTable = Table.Sort(Persons, {"count(Age)"}),

but this is not working. Any other help would really help me.

Thanks,

Shailesh

[Anonymous]

Hi, @Shailu198, I can't answer you right now, and I have to text my solution. But, for now try to create a function to count how many times a value appers

let
  MY_COUNT_FUNC = (VALUE_TO_COUNT, as text) =>
    List.Count(List.Select(Table.Column(TABLE, VALUE_TO_COUNT)))

Add a new column to you real table:

Table.AddColumn(My_Table, "Count", each MY_COUNT_FUNC(_))

And finally, sort your matrix.

Hope it helps, and sorry, but I really can't make a real solution for now. 

[Shailu198]

Hi Caaarlos,

Thanks for your reply. will try to achieve through the method you told.

Thanks 🙂 

Regards,

Shailesh

[hymieho]

Hi, check out this thread: https://community.powerbi.com/t5/Desktop/Top-amp-Bottom-10-results-without-repetitive-Rank/td-p/4249...

[Shailu198]

Hi,

How to show the Top N and Bottom N both in a single chart in Power BI? Is any option or workaround available for it?

Thanks in advance.

Regards,

Shailesh