How to Show Top 5 and Bottom 5 in Power BI with Data Labels and Format Painter

By Ravindra Bagale · Last updated: 18 October 2026

Practice files: blinkit-sales-2025-stores.csv (900 orders from 11 stores) and blinkit-store-cities-clean.csv (10 stores with state and city).

Made-up data: fictional Blinkit-style orders and stores in 2025, for practice only. Amounts are in ₹.

1. The problem

The sales table has a store_id but no state or city. The store table has state and city but no sales. You need:

1. a chart of the 5 states with the highest revenue, and
2. a chart of the 5 cities with the lowest revenue,

both with readable numbers on the bars and the same look, so they sit well side by side.

2. The idea in simple words

• Bring the location in. Merge store_cities into sales on store_id with Left Outer: every sales row stays, and state and city are added where the store is known. (Merging is explained in How to Merge Tables in Power BI Power Query (Left Outer vs Inner Join). The next chapter shows the better way for bigger models: relationships.)
• Top N works both ways. The same filter card shows the Top 5 or the Bottom 5 items by a value. Top N basics are in How to Use Filters in Power BI: Visual, Page and Report Level Filters with Top N and Advanced Filtering.
• Format Painter copies formatting (labels, fonts, colours, title style) from one visual to another. It does not copy the fields or the filters.

3. The data

After the merge, the sales table has 900 rows and total revenue ₹1,79,538. Store 111 is new and not yet in store_cities, so its 19 orders (₹2,531) have a blank state and city.

4. Step by step

Bring state and city into sales

1. Load both files and open Transform data. Select the sales query.
2. Home › Merge Queries. Second table: store_cities. Click store_id in both tables. Join Kind: Left Outer (all from first, matching from second). OK.
3. Click the expand icon on the new store_cities column. Tick only state and city, untick Use original column name as prefix, OK.
4. Close & Apply.

Top 5 states

5. Insert a Clustered column chart: X-axis state, Y-axis revenue.
6. With the chart selected: Filters pane › Filters on this visual › state › Filter type Top N › Show items Top 5 › By value revenue › Apply filter.
7. Format visual › Visual › Data labels: On. Under Values: font size 14, bold, Display units None, Value decimal places 0.
8. General › Title › text: Top 5 states by revenue.

Bottom 5 cities

9. Insert a Stacked column chart: X-axis city, Y-axis revenue.
10. Filters on this visual › city › Top N › Show items Bottom, 5 › By value revenue › Apply filter.
11. A (Blank) bar appears with 2,531: the orders of store 111. Drag state into Filters on this visual › Filter type Advanced filtering › Show items when the value is not blank › Apply filter. The Bottom 5 now shows real cities only.

Copy the look with Format Painter

12. Click the Top 5 states chart.
13. Home › Format painter (the paintbrush icon).
14. Click the Bottom 5 cities chart. Its data labels, fonts and colours now match. Change its title to Bottom 5 cities by revenue.

5. Your result should look like this

Labels read 76,450, not 76.45K. Before the "is not blank" filter, the bottom 5 was Jaipur, (Blank), Indore, Mysuru and Nagpur.

6. What happened

• Left Outer kept all 900 sales rows. An Inner join would have dropped store 111's 19 orders and shown total revenue of ₹1,77,007 instead of ₹1,79,538.
• Top 5 ranked the states by the sum of revenue and kept the highest five. Bottom 5 did the same from the other end.
• (Blank) is a real item to Power BI: the rows with no city have revenue, so they take part in the ranking. Filtering state "is not blank" removes those rows from this visual only; the card total still includes them.
• Display units None and 0 decimals show the full number. With Auto, Power BI shortens to K.
• Format Painter copied the formatting only. The city chart kept its own fields and its own Bottom 5 filter.

7. Explore further

• Bottom N and zero sales. Bottom N only ranks items that have rows in the data. A city with no orders at all does not appear; to show it you need the city from a store table related to sales (next chapter).
• Ties. If two cities tie at the cut-off, both are kept, so "Bottom 5" can show 6 bars.
• Sort. Top N does not sort the bars. Use More options (…) › Sort axis › revenue, descending for the top 5 and ascending for the bottom 5.
• Format Painter on many visuals. Click Format painter again for each extra visual. The look also works between different chart types, as far as the settings exist on both.
• Fix the blank at the source. The best fix for (Blank) is to add store 111 to store_cities. The filter is a stopgap until the store list is updated.

8. Common mistakes

9. Practise on your own

10. Think like a professional

• Business view. Top 5 shows where to invest; bottom 5 shows where to look for problems. Show both on one page.
• Say the rule in the title. "Bottom 5 cities by revenue (excluding unknown stores)" tells the reader exactly what they see.
• Treat (Blank) as a signal. A blank city means master data is missing. Report it to whoever maintains the store list.
• Consistency is a feature. Use Format Painter, or a report theme, so every chart follows the same style.

Interview questions

1. How do you show the bottom 5 cities by revenue? Filters on this visual › city › Top N › Show items Bottom 5 › By value revenue.
2. Why does (Blank) appear in a Top N chart? Rows with no matching value in the merged or related table; they have revenue, so they are ranked.
3. What does Format Painter copy? Formatting only: labels, fonts, colours, title style. Not fields or filters.
4. Left Outer or Inner when adding store details to sales? Left Outer, so no sales row is lost.

Recap

Ravi Sir's Cards for this lesson

Revision card

मराठी cards