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

     

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!