cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fordmichael20 Frequent Visitor
Frequent Visitor

Top N Within Matrix Table

Hey Power BI Community,

 

I have a question regarding potential augmentation and context-changing of Power BI Top N filter capabilities.

I have a matrix table right now that deals with positions of portfolios at a certain point in time, within the Matrix table I have the the following Rows:

Portfolio Name
Issuer [of the security] Name

I then have the market value of each issuer as a column.

What I'm trying to show Top 10 Issuers per portfolio by market value. When I select the Top N filter and change it to 10, this shows the top 10 issuers regardless of the portfolio. Is there a way to change this to have the top N refer to the first level of context in the matrix table?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Top N Within Matrix Table

Hi @fordmichael20,

 

Add a measure to your table with the following formula:

Rank_WITH_ALL =
RANKX (
    ALL ( Portfolio[Issuer] ),
    CALCULATE ( SUM ( Portfolio[Market Value] ) )
)

Then use this column in your matrix, if you need to hide it just reduce the size of the column

 

ranking.png

The second image is with the column reduced.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
Super User

Re: Top N Within Matrix Table

HI @fordmichael20,

 

Create a measure using RANKX and then use it as a visual filter and select the top 10.

 

Don't know how your data is setup but should be something like this:

Rank_Table =
RANKX (
    ALLSELECTED ( Table[Portfolio] ),
    CALCULATE ( SUM ( Table[Portfolio Amount] ) )
)

As I said don't know the format of the data but give it a try.

 

Regards,

Mfelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




fordmichael20 Frequent Visitor
Frequent Visitor

Re: Top N Within Matrix Table

@MFelix

Thanks for the help,

 

I need to take this a bit further though. The data is set up as a table for an 'as at date'. This means when I apply the measure to the table, it evaluates every market value and assigns a rank to the entire table, I need to apply a rank in the hiearchy of Portfolio->Issuer

The data would look something like this 

PortfolioIssuerMarket Value
1A3000
1B400
1C500
1D8486
1E561
1F54053
1G65
1H65
1I565
1J84
1K651
1L3
1M123
1N22
2A635
2B6510
2Y561651
2Z560
ETCETCETC

 

And I would want my matrix table to show:

PortfolioIssuerMarket Value
1F54053
 D8486
 A3000
 K651
 I565
 E561
 C500
 B400
 M123
 J84
2Y561651
 B6510
 A635
 Z560
 ETCETC
Highlighted
Super User
Super User

Re: Top N Within Matrix Table

Hi @fordmichael20,

 

Add a measure to your table with the following formula:

Rank_WITH_ALL =
RANKX (
    ALL ( Portfolio[Issuer] ),
    CALCULATE ( SUM ( Portfolio[Market Value] ) )
)

Then use this column in your matrix, if you need to hide it just reduce the size of the column

 

ranking.png

The second image is with the column reduced.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




fordmichael20 Frequent Visitor
Frequent Visitor

Re: Top N Within Matrix Table

Hey,

 

This is great, thanks so much for your help.

 

Mike