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
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...
