Forum Discussion
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
The second image is with the column reduced.
Regards,
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
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 |
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
Hey,
This is great, thanks so much for your help.
Mike
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?
where you able to do this? I am working on something similar group everything else in other category...
