How to Create Bar Chart in Power BI with Top and Bottom Parameter

Power BI is Microsoft’s powerful business intelligence tool that helps transform raw data into interactive dashboards and insightful reports. Among its many features, bar charts are widely used for comparing categorical data.

But what if you want to show only the Top 5 products or Bottom 10 regions dynamically?

That’s where Top and Bottom parameters come into play—allowing you to highlight key performers (or underperformers) based on any metric, in a way that’s both functional and beautiful.

Let’s break it all down, step-by-step.

---

📊 Understanding Bar Charts in Power BI

What is a Bar Chart?

A bar chart displays data using rectangular bars. The length of each bar represents the value of a variable. It’s great for comparison.

When and Why to Use Bar Charts?

Use them when you want to:

• Compare performance across categories (e.g., products, regions)
• Display rankings (e.g., top-selling items)
• Visualize trends without time-based analysis

---

🎯 What Are Top and Bottom Parameters?

Definition of Top N and Bottom N

Top N means showing the highest N values from your dataset based on a measure (e.g., revenue). Bottom N does the opposite—showing the lowest performers.

Why Use Top/Bottom Parameters?

• Focus on what matters most
• Reduce clutter in visuals
• Make dashboards interactive with slicers

---

🗃️ Setting Up Your Dataset

Sample Dataset Description

Imagine a retail sales dataset with columns like:

• Product Name
• Region
• Sales
• Quantity
• Profit

Loading Data into Power BI

1. Open Power BI Desktop
2. Click on Home > Get Data > Excel/CSV
3. Load your dataset and name the table SalesData

---

📈 Creating a Basic Bar Chart

1. From the Visualizations pane, select Bar Chart
2. Drag Product Name to the Axis
3. Drag Sales to the Values

You now have a basic bar chart showing sales by product.

---

🔼 Adding a Top N Parameter

Creating a Parameter Table

1. Go to Modeling > New Parameter > Numeric Range
2. Name it TopNParam
3. Set min=1, max=20, increment=1

This creates a slicer for user input.

Adding Slicer for Dynamic Input

1. Drag the newly created TopNParam to the canvas
2. Change it to a Slicer visualization

Writing DAX for Top N Logic

Create a new measure:

TopNProducts = <br>VAR TopNVal = SELECTEDVALUE('TopNParam'[TopNParam])<br>RETURN<br>CALCULATE(<br>    [Total Sales],<br>    TOPN(TopNVal, ALL('SalesData'[Product Name]), [Total Sales], DESC)<br>)<br>

Use this measure in your bar chart. It will now reflect only the Top N products based on user input.

---

🔽 Adding a Bottom N Parameter

You can either:

• Use the same slicer with toggle logic
• Or create a separate BottomNParam

DAX Logic for Bottom N Records

BottomNProducts = <br>VAR BottomNVal = SELECTEDVALUE('TopNParam'[TopNParam])<br>RETURN<br>CALCULATE(<br>    [Total Sales],<br>    TOPN(BottomNVal, ALL('SalesData'[Product Name]), [Total Sales], ASC)<br>)<br>

Use this in a separate chart or switch using bookmarks.

---

🔁 Combining Top and Bottom in One Chart

Dual-Measure Approach

To show both in one chart:

1. Create a new table with UNION of Top and Bottom values
2. Use a custom column to label them as “Top” or “Bottom”

Handling Overlapping Records

Use EXCEPT to ensure no product appears in both:

DAXCopyEditTopOnly = EXCEPT(TopTable, BottomTable)

Using Bookmarks to Switch Views

Use bookmarks to toggle between:

• Top N Chart
• Bottom N Chart
• Combined View

Add buttons and link them with bookmark actions.

---

🖊️ Dynamic Titles and Labels

Create a DAX measure for the title:

DynamicTitle = <br>"Top " & SELECTEDVALUE('TopNParam'[TopNParam]) & " Products by Sales"<br>

Add a Card or Textbox to use this dynamically.

---

🎨 Formatting Tips for Clear Visuals

• Use contrasting colors for Top vs Bottom
• Enable data labels for values
• Sort data in descending/ascending order
• Customize tooltips to show additional info like Profit %

---

📈 Advanced Use Cases

Top & Bottom Products by Sales

Compare which products perform best/worst and why.

Top & Bottom Customers by Revenue

Identify high-value clients vs churn risks.

Dynamic KPIs

Let users switch between:

• Revenue
• Profit
• Quantity
  And apply Top/Bottom filters accordingly.

---

🚫 Common Mistakes to Avoid

• Forgetting to remove filters on the axis (use ALL)
• Including blanks in Top N logic
• Writing DAX without wrapping with CALCULATE

---

⚡ Performance Optimization

• Use VAR for reusable logic
• Avoid nested IFs inside measures
• Pre-aggregate data in Power Query if needed

---

💼 Real-World Use Case Demo

Scenario: A retail manager wants to see the top 5 and bottom 5 products based on weekly sales.

Solution:

• Use slicers for week & Top N input
• Create two bar charts with Top/Bottom DAX
• Add dynamic titles and combine in a dashboard

Result: Quick insights, focused decision-making, and cleaner visual storytelling.

---

✅ Conclusion

Creating a bar chart in Power BI with Top and Bottom N parameters is a game-changer for storytelling with data. It allows users to focus only on what’s important, interact with the dashboard, and make real-time decisions.

From setting up parameters to writing DAX and formatting charts—this guide covered everything you need.

Start using this feature today to make your dashboards smarter and more insightful.

---

🙋 FAQs

1. How do I limit Top N dynamically in Power BI?
Use a parameter table or slicer to allow user input and apply TOPN with SELECTEDVALUE() in your DAX.

2. Can I use multiple slicers with Top N logic?
Yes, you can filter by region, date, or category alongside your Top N slicer.

3. What if my dataset is too large?
Use aggregated tables or summary views in Power Query to reduce load.

4. Can I combine Top N across multiple categories?
Yes, but be mindful of overlapping values and use grouping or composite keys.

5. How do I avoid duplicates in Top & Bottom?
Use EXCEPT() or apply rank filters to ensure uniqueness in both sets.