Top 5 & Bottom 5 both in Matrix Visual

[ianallen13D]

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?  

[Uzi2019 · Accepted solution]

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!

[nandic]

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]

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