Forum Discussion

shubh25's avatar
shubh25
Icon for Helper I rankHelper I
7 years ago
Solved

Top N Slicer in a matrix?

Hi,

I need this Top N filter/slicer in the form of a slicer in visual so that we are able to see top 5,10 based on the value of balance column and in this matrix form. I am able to do this using RankX in table form but all goes haywire in matrix form.

Is there a solution?


Attaching the link to sample data.

https://www.dropbox.com/sh/u3bmhm9gosvl2te/AACcbXbs-8jWdh_CMgnz5MTSa?dl=0

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi shubh25 

     

    Please see the attached solution, I've added an extra table "Top N selector" for filtering and adjusted Rank Balance Measure and Visual filter to reflect the changes.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

     

7 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi shubh25 

     

    what would you expect to see as an outcome, top n clients for each Source.Name?

     

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi shubh25 

     

    Please see the attached solution, I've added an extra table "Top N selector" for filtering and adjusted Rank Balance Measure and Visual filter to reflect the changes.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

     

    • shubh25's avatar
      shubh25
      Icon for Helper I rankHelper I
      7 years ago

      Hi Mariusz,
      Thank you very much,
      this solved the top N matrix problem I was having.
      Just wondering if it also works in another way. e.g., when chosen top 3, it shows only 3 even if 2 or more than 2 source name are selected. 
      I tried it by removing the sourcename from the visual and it worked but is there a way that we can have source name as well as only the top 3 throughout the table?

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi shubh25 
        The below will rank top n on Client and Source.Name granularity. 

        Rank Balance 2 = 
IF (
    ISINSCOPE( 'TOP N with Matrix'[Client] ),
    INT(
        RANKX (
            CALCULATETABLE (
                GROUPBY('TOP N with Matrix', 'TOP N with Matrix'[Client], 'TOP N with Matrix'[Source.Name] ),
                ALLSELECTED ( 'TOP N with Matrix'[Client], 'TOP N with Matrix'[Source.Name]  )
            ),
            CALCULATE( SUM( 'TOP N with Matrix'[Balance] ) )
        ) <= MAX( 'Top N Selector'[Value] )
    )
)

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski