Reply
Highlighted
Regular Visitor
Posts: 16
Registered: ‎03-20-2017
Accepted Solution

Top N and Bottom N in single chart

Hi,

 

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

 

Regards,

Shailesh


Accepted Solutions
Member
Posts: 54
Registered: ‎03-03-2017

Re: Top N and Bottom N in single chart

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.

View solution in original post


All Replies
Regular Visitor
Posts: 16
Registered: ‎03-20-2017

Top N and Bottom N in single chart

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

Member
Posts: 54
Registered: ‎03-03-2017

Re: Top N and Bottom N in single chart

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.

Regular Visitor
Posts: 16
Registered: ‎03-20-2017

Re: Top N and Bottom N in single chart

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

Member
Posts: 54
Registered: ‎03-03-2017

Re: Top N and Bottom N in single chart

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. 

 

Regular Visitor
Posts: 16
Registered: ‎03-20-2017

Re: Top N and Bottom N in single chart

Hi Caaarlos,

 

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

Thanks Smiley Happy 

 

 

Regards,

Shailesh

Regular Visitor
Posts: 25
Registered: ‎09-28-2016

Re: Top N and Bottom N in single chart