Forum Discussion

fordmichael20's avatar
fordmichael20
Frequent Visitor
9 years ago
Solved

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?

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    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

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    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's avatar
      fordmichael20
      Frequent Visitor
      9 years ago

      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
      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        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