Top N Within Matrix Table

[fordmichael20]

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?

[MFelix]

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

[fordmichael20]

@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 

Portfolio	Issuer	Market Value
1	A	3000
1	B	400
1	C	500
1	D	8486
1	E	561
1	F	54053
1	G	65
1	H	65
1	I	565
1	J	84
1	K	651
1	L	3
1	M	123
1	N	22
2	A	635
2	B	6510
2	Y	561651
2	Z	560
ETC	ETC	ETC

And I would want my matrix table to show:

Portfolio	Issuer	Market Value
1	F	54053
	D	8486
	A	3000
	K	651
	I	565
	E	561
	C	500
	B	400
	M	123
	J	84
2	Y	561651
	B	6510
	A	635
	Z	560
	ETC	ETC

[MFelix · Accepted solution]

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

The second image is with the column reduced.

Regards,

MFelix

[Aydeedglz]

Hi! I am trying to do the same but adding a "Other" row in the matrix for those below to 21 (I am doing top 20) how can I do that?

[paulasanta]

where you able to do this? I am working on something similar group everything else in other category...

[MFelix]

Hi @paulasanta ,

What are your needs?

[fordmichael20]

Hey,

This is great, thanks so much for your help.

Mike