Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi! 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...
Hi @paulasanta ,
What are your needs?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey,
This is great, thanks so much for your help.
Mike
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |