First Power BI report - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a simple report showing total sales by product category and month to understand sales trends.

📊 Data: You have sales data with columns: Date, Product Category, Sales Amount.

🎯 Deliverable: Create a Power BI report with a table and a line chart showing total sales by product category and month.

Sample Data

Date	Product Category	Sales Amount
2024-01-15	Electronics	1200
2024-01-20	Clothing	800
2024-02-05	Electronics	1500
2024-02-10	Clothing	700
2024-03-12	Electronics	1300
2024-03-15	Clothing	900
2024-01-25	Home Goods	600
2024-02-20	Home Goods	750
2024-03-22	Home Goods	800

1

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

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the table with columns Date, Product Category, Sales Amount.

Expected Result

Sales data is loaded and visible in the Fields pane.

2

Step 2: Create a new column to extract the month and year from the Date column for grouping.

New Column formula: MonthYear = FORMAT('Table'[Date], "yyyy-MM")

Expected Result

A new column 'MonthYear' is added showing values like '2024-01', '2024-02', etc.

3

Step 3: Create a measure to calculate total sales amount.

Total Sales = SUM('Table'[Sales Amount])

Expected Result

A measure 'Total Sales' is created that sums sales amounts.

4

Step 4: Add a table visual to the report canvas showing Product Category, MonthYear, and Total Sales.

Drag 'Product Category' and 'MonthYear' to Rows, and 'Total Sales' to Values in the table visual.

Expected Result

Table shows total sales by product category and month.

5

Step 5: Add a line chart visual to show sales trends over months for each product category.

Set Axis to 'MonthYear', Legend to 'Product Category', and Values to 'Total Sales'. Sort Axis by MonthYear ascending.

Expected Result

Line chart displays sales trends by month for each product category.

6

Step 6: Format visuals for clarity: add titles, adjust colors for contrast, and enable data labels on the line chart.

Use Visualizations pane to set titles like 'Sales by Category and Month', choose distinct colors, and turn on data labels.

Expected Result

Report visuals are clear, readable, and accessible.

Final Result

Power BI Report

+-----------------------------------------+
| Sales by Category and Month              |
|-----------------------------------------|
| Product Category | Month  | Total Sales |
|------------------|--------|-------------|
| Electronics      | 2024-01| 1200        |
| Electronics      | 2024-02| 1500        |
| Electronics      | 2024-03| 1300        |
| Clothing         | 2024-01| 800         |
| Clothing         | 2024-02| 700         |
| Clothing         | 2024-03| 900         |
| Home Goods       | 2024-01| 600         |
| Home Goods       | 2024-02| 750         |
| Home Goods       | 2024-03| 800         |
+-----------------------------------------+

Line Chart: Sales Trends

Month: 2024-01 -> 2024-03
Lines: Electronics, Clothing, Home Goods
Sales values rising and falling per category over months.

✓Electronics category has the highest sales overall.

✓Sales for Electronics peaked in February 2024.

✓Clothing sales are steady but lower than Electronics.

✓Home Goods sales are the lowest but show a slight increase over the months.

Bonus Challenge

Add a slicer to the report to filter sales by Product Category interactively.

Show Hint

Use the Slicer visual, drag 'Product Category' field into it, so users can select one or multiple categories to filter the report.

Practice

1. What is the main purpose of creating a Power BI report?

easy

A. To create text documents for data

B. To write complex code for data processing

C. To store large amounts of raw data

D. To turn data into easy-to-understand visuals

Solution

1. Step 1: Understand Power BI report function

   Power BI reports are designed to visualize data, making it easier to understand.

2. Step 2: Compare options with report purpose

   Only turning data into visuals matches the report's main goal.

3. Final Answer:

   To turn data into easy-to-understand visuals -> Option D

4. Quick Check:

   Power BI reports = Visual data display [OK]

Hint: Reports = visuals, not coding or raw data [OK]

Common Mistakes:

• Thinking Power BI is for coding data processing
• Confusing reports with data storage
• Assuming reports create text documents

2. Which of the following is the correct way to add a field to a Power BI report visualization?

easy

A. Type the field name in a text box on the report

B. Drag the field from Fields pane to the report canvas

C. Write a SQL query to add the field

D. Upload the field as a separate file

Solution

1. Step 1: Recall how to add fields in Power BI

   Fields are added by dragging them from the Fields pane to the report canvas.

2. Step 2: Eliminate incorrect methods

   Writing SQL, typing names, or uploading files are not how fields are added to visuals.

3. Final Answer:

   Drag the field from Fields pane to the report canvas -> Option B

4. Quick Check:

   Drag fields to visuals = correct method [OK]

Hint: Drag fields from Fields pane to canvas [OK]

Common Mistakes:

• Trying to write SQL to add fields
• Typing field names manually
• Uploading fields as files

3. Given a dataset with sales data, what will happen if you drag the 'Sales Amount' field to the report canvas and select a Card visualization?

medium

A. It will show a blank report because Card needs a measure

B. It will list all sales transactions in a table

C. It will show the total sales amount as a single number

D. It will create a bar chart of sales by product

Solution

1. Step 1: Understand Card visualization purpose

   Card visuals display a single summarized number, like total sales.

2. Step 2: Analyze dragging 'Sales Amount' to Card

   Dragging a numeric field like 'Sales Amount' to Card shows its total sum.

3. Final Answer:

   It will show the total sales amount as a single number -> Option C

4. Quick Check:

   Card visual = single total number [OK]

Hint: Card shows one total number from numeric field [OK]

Common Mistakes:

• Expecting a table or chart from Card
• Thinking Card needs a special measure
• Confusing Card with bar chart

4. You created a report but the visual shows no data. Which of these is the most likely reason?

medium

A. You forgot to drag any fields into the visual

B. You used the wrong font color

C. You saved the report without a name

D. You closed Power BI Desktop

Solution

1. Step 1: Identify why visuals show no data

   Visuals need fields dragged into them to display data.

2. Step 2: Check other options for impact

   Font color or report name do not affect data display; closing Power BI stops work but is unrelated to visual data.

3. Final Answer:

   You forgot to drag any fields into the visual -> Option A

4. Quick Check:

   No fields dragged = no data shown [OK]

Hint: Visuals need fields dragged in to show data [OK]

Common Mistakes:

• Blaming font color for no data
• Thinking report name affects visuals
• Confusing closing app with visual data issue

5. You want to create a report that lets users filter sales by year and see total sales update instantly. Which two visuals should you use together?

hard

A. A slicer for Year and a Card for total sales

B. A table for Year and a pie chart for sales

C. A text box for Year and a bar chart for sales

D. A map for Year and a line chart for sales

Solution

1. Step 1: Identify filtering visual

   Slicers let users pick filter values like Year interactively.

2. Step 2: Identify visual showing total sales

   Card visual shows a single total number that updates with filters.

3. Step 3: Combine slicer and card for interactive filtering

   Using slicer for Year and Card for total sales lets users filter and see instant updates.

4. Final Answer:

   A slicer for Year and a Card for total sales -> Option A

5. Quick Check:

   Slicer + Card = interactive filter and total [OK]

Hint: Use slicer to filter and card to show total [OK]

Common Mistakes:

• Using text box instead of slicer for filtering
• Choosing visuals that don't update interactively
• Confusing map with year filter