Dynamically Display Top N and Bottom N Records in One Power BI Chart (2024)

By: Kenneth A. Omorodion |Updated: 2024-06-13 |Comments | Related: > Power BI Charts


Problem

Recently, I wrote an article,Rank and Sort Data Based on Multiple Columns in Power BI Using DAX. However,it is very common for business users to request the ability to dynamically viewthe Top N and Bottom N values of a measure, like Total Sales, on the same visual.This requirement is simple to implement on either the Top or Bottom N options. But,the challenge is when we need to represent the two options on the same chart simultaneously.

Solution

This article will demonstrate step-by-step how to dynamically represent Top Nand Bottom N in the same Power BI chart. However, I recommend that you readmyother article before reading this one, as I will be referencing it since itexplains how the ranking part is done using different approaches.

For this demo, I will leverage a data model with a Fact table called "Sales"(below). Of course, I will only show a snapshot of data rows within the table.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (1)

I would also use a Dimension table called "Product"(below), which has more rows of data.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (2)

The requirement is as follows: On a bar chart, we need to show the top five andbottom five Products from the Products tablebased on the Total Sales value derived from the Sales table.The anticipated output should look like the image below.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (3)

Now that we understand the requirements, outlined below are the steps to follow.Remember to establish a relationship between your tables before you continue.

  1. Create a measure to summarize the Revenue on the Sales fact table.
  2. Create a measure to rank the measure created in step 1 and then apply alogic to render the top and bottom N in the visual.
  3. Apply the measure in step 2 to the Bar Chart visual.
  4. Apply a conditional formatting if required.
  5. Compare the output on the Top and Bottom N visual to when no DAX logic isapplied.

Step 1: Create a Measure to Summarize the Revenue on the Sales Fact Table

This step is very straightforward. You need to create a measure on theRevenue column as follows. In this demo, I have referred to this measure as "Sales."

Sales = SUM(Sales[Revenue])

Step 2: Create a Measure to Rank the Measure Created in Step 1 and Apply a Logicto Render the Top and Bottom N in the Visual

In this step, we will create a measure that first ranks the products and thenapplies a logic to only pull the Top N and Bottom N values and represent them onthe Bar Chart, as seen in the final output above.

To create this measure, use or adapt the following DAX code:

 Rank Value (Sales) = VAR _rank_top = RANKX ( ( ALLSELECTED ( 'Product'[Product] ) ), Sales[Sales],, DESC, DENSE )VAR _rank_bottom = RANKX ( ( ALLSELECTED ( 'Product'[Product] ) ), Sales[Sales],, ASC, DENSE )VAR _result = IF ( _rank_top <= 5, Sales[Sales], IF ( _rank_bottom <= 5, Sales[Sales], BLANK () ) )RETURN _result 

Step 3: Apply the Measure in Step 2 to the Bar Chart Visual

To do this, select a bar chart among the visuals in Power BI. Note: In your case,this might be other visuals. But a bar chart is best to demonstrate in this tip.

In the image below, you can see that I have included the "Product"column from the Products table into the Y-axis well and the newmeasure created in Step 2 in the X-axis well.

This ensures that only the Top 5 and Bottom 5 products are rendered on the visualbased on the total Sales value.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (4)

Step 4: Apply Conditional Formatting, If Required

This is an optional step. If you are interested in applying conditional formattingto help easily differentiate between the Top 5 and Bottom 5 products by sales, thenfollow this step. I have used bar color as the conditional formatting for this demonstration.I will use green bars and red bars for Top N and Bottom N, respectively.

The conditional formatting window can be seen in the image below. Note: I havenot applied any logic for the conditional formatting to identify the Bottom N orTop N ranges. However, for simplicity, I have hardcoded the values in this tip justto show the concept. In your case, it might be obvious what to do. For example,you might have all your Bottom N values as negative values; thus, it would be easierto implement the conditional formatting.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (5)

The output would look like the image below.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (6)

Step 5: Compare the Output on the Top and Bottom N Visual to When No DAX Logicis Applied

This step demonstrates how to figure out if the output of the previous steps(particularly Step 3) has yielded the desired outcomes based on business requirements.

To do this, create a new bar chart visual with only the "Sales"measure created in Step 1, as well as the Products columnfrom the Products table, as seen in the image below.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (7)

When you compare the above bar chart with the bar char for the Top and Bottom5 created earlier, you can see that they match for the Top 5 bars, representingthe Top 5.

Let's do the same for the bottom values by taking a snapshot of the bottompart of the image above. This shows that the DAX code we wrote earlier inStep 2 has dynamically pulled the bottom 5 products based on sales. See the imagebelow.

Dynamically Display Top N and Bottom N Records in One Power BI Chart (8)

In summary, this article successfully demonstrated how to represent Top N andBottom N on the same chart using DAX in Power BI. The N value is not fixed, allowingyou to adjust it according to your business needs. This logic can also work if youuse a rank of multiple fields from different or the same tables, but you would needto apply the logic used in my earlier article.

Next Steps

Learn more about Power BI in this 3 hour training course.


Click here to start the Power BI course




About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-06-13

Dynamically Display Top N and Bottom N Records in One Power BI Chart (2024)
Top Articles
Latest Posts
Article information

Author: Rev. Porsche Oberbrunner

Last Updated:

Views: 5811

Rating: 4.2 / 5 (53 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Rev. Porsche Oberbrunner

Birthday: 1994-06-25

Address: Suite 153 582 Lubowitz Walks, Port Alfredoborough, IN 72879-2838

Phone: +128413562823324

Job: IT Strategist

Hobby: Video gaming, Basketball, Web surfing, Book restoration, Jogging, Shooting, Fishing

Introduction: My name is Rev. Porsche Oberbrunner, I am a zany, graceful, talented, witty, determined, shiny, enchanting person who loves writing and wants to share my knowledge and understanding with you.