Sorting data in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the top-selling products sorted by total sales amount to identify best performers.

📊 Data: You have sales data including Product Name, Category, Units Sold, and Sales Amount for the last quarter.

🎯 Deliverable: Create a Power BI report page that shows a table of products sorted by Sales Amount in descending order.

Sample Data

Product Name	Category	Units Sold	Sales Amount
Wireless Mouse	Accessories	150	3750
USB-C Charger	Accessories	120	3600
Bluetooth Speaker	Audio	80	5600
Gaming Keyboard	Accessories	90	8100
HD Monitor	Displays	60	12000
Webcam	Accessories	110	4400
External Hard Drive	Storage	70	7000
Smartphone Stand	Accessories	130	1950

1

Step 1: Load the sales data into Power BI Desktop by importing the data source.

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the table.

Expected Result

Sales data table appears in the Fields pane with columns: Product Name, Category, Units Sold, Sales Amount.

2

Step 2: Create a table visual on the report canvas.

From the Visualizations pane, select the 'Table' visual.

Expected Result

An empty table visual appears on the report page.

3

Step 3: Add 'Product Name' and 'Sales Amount' fields to the table visual.

Drag 'Product Name' and 'Sales Amount' from Fields pane into the Values area of the table visual.

Expected Result

The table shows each product with its corresponding sales amount.

4

Step 4: Sort the table by 'Sales Amount' in descending order.

Click the down arrow next to 'Sales Amount' column header in the table visual and select 'Sort descending'.

Expected Result

The table rows reorder so that the product with the highest sales amount is at the top.

5

Step 5: Format the Sales Amount column to show currency.

Select the Sales Amount field in the Fields pane, then in the Column tools tab, set Data type to 'Decimal Number' and Format to 'Currency'.

Expected Result

Sales Amount values display with currency symbol and two decimal places.

Final Result

-----------------------------------
| Product Name       | Sales Amount |
-----------------------------------
| HD Monitor         | $12,000.00   |
| Gaming Keyboard    | $8,100.00    |
| External Hard Drive| $7,000.00    |
| Bluetooth Speaker  | $5,600.00    |
| Webcam             | $4,400.00    |
| Wireless Mouse     | $3,750.00    |
| USB-C Charger      | $3,600.00    |
| Smartphone Stand   | $1,950.00    |
-----------------------------------

✓HD Monitor is the top-selling product with $12,000 in sales.

✓Gaming Keyboard and External Hard Drive also have strong sales above $7,000.

✓Smartphone Stand has the lowest sales among the listed products.

Bonus Challenge

Add a slicer to filter the table by product category and observe how the sorting updates dynamically.

Show Hint

Use the 'Slicer' visual and add the 'Category' field to it. Then select categories to filter the table.

Practice

1. What is the main purpose of sorting data in Power BI reports?

easy

A. To create new columns in the dataset

B. To delete unnecessary data from the report

C. To change data values automatically

D. To organize data so reports are clearer and easier to read

Solution

1. Step 1: Understand sorting purpose

   Sorting arranges data in a specific order to improve readability and clarity.

2. Step 2: Identify correct purpose in options

   Only To organize data so reports are clearer and easier to read describes organizing data to make reports clearer and easier to read.

3. Final Answer:

   To organize data so reports are clearer and easier to read -> Option D

4. Quick Check:

   Sorting = Organizing data for clarity [OK]

Hint: Sorting arranges data to improve report clarity [OK]

Common Mistakes:

• Confusing sorting with deleting data
• Thinking sorting changes data values
• Believing sorting creates new columns

2. Which of the following is the correct way to sort a column by another column in Power BI?

easy

A. Select the column, then use 'Sort by Column' and choose the other column

B. Right-click the column and select 'Delete Column'

C. Drag the column to the report canvas

D. Use 'Sort Ascending' on the other column only

Solution

1. Step 1: Recall sorting by another column method

   In Power BI, to sort a column by another, you select the column and use 'Sort by Column' feature.

2. Step 2: Match method to options

   Select the column, then use 'Sort by Column' and choose the other column correctly describes this process; other options do unrelated actions.

3. Final Answer:

   Select the column, then use 'Sort by Column' and choose the other column -> Option A

4. Quick Check:

   Sort by Column feature = Select the column, then use 'Sort by Column' and choose the other column [OK]

Hint: Use 'Sort by Column' to order one column by another [OK]

Common Mistakes:

• Trying to delete columns instead of sorting
• Dragging columns to canvas does not sort
• Using 'Sort Ascending' alone won't sort by another column

3. Given a table with columns MonthName and MonthNumber, which sorting will display months in calendar order in a visual?

medium

A. Sort MonthName by MonthNumber

B. Sort MonthNumber by MonthName

C. Sort MonthName alphabetically

D. Sort MonthNumber descending

Solution

1. Step 1: Understand sorting for calendar order

   Months should be shown in calendar order, which is numeric order of MonthNumber.

2. Step 2: Choose sorting method

   Sorting MonthName by MonthNumber arranges month names in calendar order.

3. Final Answer:

   Sort MonthName by MonthNumber -> Option A

4. Quick Check:

   MonthName sorted by MonthNumber = calendar order [OK]

Hint: Sort names by numbers for correct calendar order [OK]

Common Mistakes:

• Sorting MonthNumber by MonthName causes wrong order
• Alphabetical sort of MonthName is incorrect calendar order
• Descending MonthNumber reverses calendar order

4. You tried to sort a 'Product Category' column by 'Category ID' but the visual still shows alphabetical order. What is the likely issue?

medium

A. The data model is missing a relationship

B. The 'Category ID' column has duplicate values

C. You did not select the 'Product Category' column before applying 'Sort by Column'

D. You sorted the 'Category ID' column instead of 'Product Category'

Solution

1. Step 1: Understand sorting by column requirement

   To sort one column by another, you must select the column to sort first.

2. Step 2: Identify common mistake

   If the visual still shows alphabetical order, likely the 'Product Category' column was not selected before sorting.

3. Final Answer:

   You did not select the 'Product Category' column before applying 'Sort by Column' -> Option C

4. Quick Check:

   Select column first to sort by another [OK]

Hint: Always select the column to sort before choosing 'Sort by Column' [OK]

Common Mistakes:

• Sorting wrong column by mistake
• Ignoring duplicate values in sort column
• Assuming relationships affect sorting here

5. You have a sales table with a 'Region' column and a separate 'Region Sort Order' table. How do you ensure a bar chart sorts regions by the custom order defined in 'Region Sort Order'?

hard

A. Sort the 'Region Sort Order' column alphabetically

B. Create a relationship between tables and sort 'Region' column by 'Region Sort Order' column

C. Sort the bar chart manually by dragging bars

D. Use a slicer to filter regions in desired order

Solution

1. Step 1: Understand sorting with related tables

   To sort by a custom order in another table, a relationship must exist between the tables.

2. Step 2: Apply sorting by related column

   Sort the 'Region' column by the 'Region Sort Order' column from the related table to enforce custom order in visuals.

3. Final Answer:

   Create a relationship between tables and sort 'Region' column by 'Region Sort Order' column -> Option B

4. Quick Check:

   Relationship + sort by related column = custom order [OK]

Hint: Link tables and sort main column by related order column [OK]

Common Mistakes:

• Sorting order column alphabetically loses custom order
• Trying to drag bars does not persist sorting
• Using slicers does not change sort order