Forum Discussion
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
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.
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] ) ) ) )
5 Replies
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] ) ) ) )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?
Previous solutions related to line charts and scatter graphs and I couldn't seem to get them to work in this table form.
Refer to this solution
https://www.youtube.com/watch?v=L8Rr8u8MyxU&ab_channel=PowerBIHelpline
