Forum Discussion

ianallen13D's avatar
ianallen13D
Frequent Visitor
2 years ago
Solved

Top 5 & Bottom 5 both in Matrix Visual

Hello, 

 

I have a matrix visual that is basically showing the following: 

Fields are: 

Status - basically parent category to Task (i.e. Advance, Develop, etc.)

Task - the name of the task what was completed

 

Then I have a column called "# of Completed Tasks" 

 

  

 

I am being asked to provide the top 5 tasks and the bottom 5 tasks.  It's already filtered for top 5, but I didn't know how to do both top 5 and bottom 5 in the same matrix visual.  Has anyone done this before?

So basically what I'm looking for under each status category is something like this...

 

 

Anyone have any easy ideas?  

General Comment
Need Help
Tips and Tricks
  • Uzi2019's avatar
    Uzi2019
    2 years ago

    Hi   

    Modify dax as per you need.

    Top Bottom = 
VAR __Products = 
FILTER ( 
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            ALLSELECTED ( Products[Product] ), 
            Products[Product] 
        ), 
        "@Sale", [Sales] 
    ), 
    NOT ISBLANK ( [@Sale] ) 
)  
RETURN
CALCULATE ( 
    [Sales],
    KEEPFILTERS ( 
        UNION (
            TOPN ( 5, __Products, [@Sale], ASC ),
            TOPN ( 5, __Products, [@Sale] )
        )
    )
)

     

     

    Or refer below videos
    https://www.youtube.com/watch?v=mdj2ilk4rGc

     

     

    I hope I answered your question!

     

3 Replies

  • nandic's avatar
    nandic
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Create measure like this:

    TopBottom =
        IF(
            ISBLANK(CALCULATE(SUM('Table'[Counter]))),
            BLANK(),
            IF(
                RANKX(
                           ALL('Table'[Task]),
                          CALCULATE(SUM('Table'[Counter])), , ASC) <= 5 ||
          RANKX(
                        ALL('Table'[Task]),
                       CALCULATE(SUM('Table'[Counter])), , DESC) <= 5,
                1,
                0
            )
        )

    The result is 1 or 0.
    1 if task completed # is in top 5 or bottom 5, otherwise it will return 0.

    And then just use that measure as visual filter where you will set measure TopBottom is equal to 1 to only show top/bottom 5 tasks.

    The key is to use function rankx with asc to get top tasks, desc to get bottom tasks.

    In screenshots below i used top/bottom 3. Image on left is raw data, image on the right is where i used measure as a filter.

     



    Cheers,
    Nemanja Andic
    • ianallen13D's avatar
      ianallen13D
      Frequent Visitor
      2 years ago

      Thank you so much!!  Both options are very helpful.  I really appreciate you guys!

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi   

    Modify dax as per you need.

    Top Bottom = 
VAR __Products = 
FILTER ( 
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            ALLSELECTED ( Products[Product] ), 
            Products[Product] 
        ), 
        "@Sale", [Sales] 
    ), 
    NOT ISBLANK ( [@Sale] ) 
)  
RETURN
CALCULATE ( 
    [Sales],
    KEEPFILTERS ( 
        UNION (
            TOPN ( 5, __Products, [@Sale], ASC ),
            TOPN ( 5, __Products, [@Sale] )
        )
    )
)

     

     

    Or refer below videos
    https://www.youtube.com/watch?v=mdj2ilk4rGc

     

     

    I hope I answered your question!