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

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

      • fordmichael20's avatar
        fordmichael20
        Frequent Visitor
        9 years ago

        Hey,

         

        This is great, thanks so much for your help.

         

        Mike