In Power BI, Top N and Bottom N are often displayed using a visual level filter and two visuals, respectively. As a result, we can only show one item at a time on top or bottom N. But, if the situation asks for slicer selection to be used to dynamically change how top and bottom N are shown in a single visual at simultaneously. DAX can help us accomplish this scenario. Let’s look at how we can visualize this situation.
1. Create a measure like Total Quantity to display the total number of orders.
Total Order = COUNT(order details[order_id])
2. Create a table and generate a series in that table by using the below Dax which will be used for slicer selection.
Slicer = (GENERATESERIES(1,10))
3. Now create a measure that will be used on the visual level filter to display Top and Bottom N both in a single visual at the same time.
Top Bottom =
Var TopX= SELECTEDVALUE(Slicer[Value Selection])
Var TopRank= RANKX(ALL(pizza_types[name]),CALCULATE(‘Calculations'[Total Order]),,DESC)
Var BottomRank= RANKX(ALL(pizza_types[name]),CALCULATE(‘Calculations'[Total Order]),,ASC)
Unleash the Potential of Power Platform With a Center of Excellence
Business innovation often comes from within. Discover how to empower innovation from non-traditional developers with the Microsoft Power Platform.
Explanation: In the above measure
- In TopX we are taking the values which are there in the column which we created for slicer selection.
- In TopRank we are giving rank to pizza names and their Total number of orders in Descending order.
- In BottomRank we are giving rank to pizza names and their Total number of orders in Ascending order.
- Then in Result, we are putting the Condition that if TopRank is less than or equal to TopX then give the result as 1, and if BottomRank is less than or equal to TopX then give the result as -1 else give blank.
- Then we are returning the Result.
4. Now place the column in the slicer from the table in which we generated the series.
5. Now take the bar chart on canvas and drag the total orders in the value section and pizza names on X-axis and Place the Top Bottom measure in the Visual level filter, select is not blank, and apply the filter.
6. Now we want to display top orders in green and bottom orders in red then we can give conditional formatting to the visual. We need to make a measure and place it in the data color function.
Measure:> Color = SWITCH(TRUE(),
That’s how we can display Top and Bottom N both in Single Visual at the same time in Power BI.