Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
george_o0802
Regular Visitor

Top 5 and Bottom 5 in same table

Previous solutions don't seem to work so I need some clearer guidance I think. Below is what I want the results to look like

george_o0802_1-1694793980429.png

 


I have ranked a set of products by RSV and a YoY rank change. I want to show the top 5 and bottom 5 in a single table of 11 rows (title and 10 products).

In the above example I have just created two tables, used a Top N filter and hidden the column headers of the bottom table. This is obviously not the best way to go about it so if anyone can help me create this, I would be very appreciative.

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@george_o0802 you can easily achieve this by adding following measure:

 

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] )
        )
    )
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@george_o0802 you can easily achieve this by adding following measure:

 

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] )
        )
    )
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

You're a wizard. Thank you.

Just so I can understand how it works a bit better as I'm relatively new to power bi and dax, I have a couple of questions.

1) Why do we put @ symbols in front of the embedded measures?


2) For whatever reason, all other titles that are not in the top 5 or bottom 5 are coming up with a rank change of 53. Why is this? It's simple enough as I can just filter out any value that is 53 but I was just curious why this was coming up and if this is a by product of something you have done here?

Ahmedx
Super User
Super User

parry2k
Super User
Super User

@george_o0802 what was the previous solution?

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Previous solutions related to line charts and scatter graphs and I couldn't seem to get them to work in this table form.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.