Line charts 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 how monthly sales have changed over the past year to identify trends and seasonal patterns.

📊 Data: You have a table with sales data including columns for Date (month and year), Product Category, and Sales Amount.

🎯 Deliverable: Create a line chart showing total sales amount for each month over the last 12 months.

Sample Data

Date	Product Category	Sales Amount
2023-07	Electronics	15000
2023-07	Clothing	8000
2023-08	Electronics	17000
2023-08	Clothing	9000
2023-09	Electronics	16000
2023-09	Clothing	8500
2023-10	Electronics	18000
2023-10	Clothing	9500
2023-11	Electronics	20000
2023-11	Clothing	10000
2023-12	Electronics	22000
2023-12	Clothing	11000

1

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

Use 'Get Data' to import the sales data with columns Date, Product Category, and Sales Amount.

Expected Result

Sales data is available in Power BI for analysis.

2

Step 2: Create a new measure to calculate total sales per month.

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

Expected Result

A measure named 'Total Sales' that sums sales amounts.

3

Step 3: Add a line chart visual to the report canvas.

Insert > Line Chart visual.

Expected Result

An empty line chart appears on the report.

4

Step 4: Set the X-axis to the Date column and the Y-axis to the Total Sales measure.

Drag 'Date' to X-axis and 'Total Sales' measure to Y-axis fields of the line chart.

Expected Result

Line chart shows total sales trend over months.

5

Step 5: Format the Date axis to show month and year clearly and sort chronologically.

In X-axis properties, set type to 'Categorical' and sort by Date ascending.

Expected Result

X-axis displays months in correct order from oldest to newest.

6

Step 6: Add chart title 'Monthly Sales Trend' and label axes for clarity.

Chart title: 'Monthly Sales Trend'; X-axis label: 'Month'; Y-axis label: 'Total Sales ($)'.

Expected Result

Chart is clearly titled and axes are labeled.

Final Result

Monthly Sales Trend

 25000 |                      *
       |                     * 
 20000 |                  *  *  
       |                 *      
 15000 |            *  *         
       |           *             
 10000 |      *  *               
       |                         
  5000 |                         
       +-------------------------
        Jul Aug Sep Oct Nov Dec

✓Sales increased steadily from July to December.

✓December had the highest sales, indicating a seasonal peak.

✓Electronics consistently contributed more sales than Clothing.

Bonus Challenge

Create a line chart that shows monthly sales trends separately for each product category.

Show Hint

Use the 'Product Category' field as a legend in the line chart to display separate lines.

Practice

1. What is the main purpose of a line chart in Power BI?

easy

A. To list raw data values in a table format

B. To display data as separate categories without order

C. To show trends and changes over time or order

D. To compare parts of a whole using slices

Solution

1. Step 1: Understand the function of line charts

   Line charts are designed to show how data changes over time or a sequence.

2. Step 2: Compare with other chart types

   Other charts like pie charts show parts of a whole, tables show raw data, and bar charts show categories without order.

3. Final Answer:

   To show trends and changes over time or order -> Option C

4. Quick Check:

   Line chart = trends over time [OK]

Hint: Line charts always show data changes over time or order [OK]

Common Mistakes:

• Confusing line charts with pie charts
• Using line charts for unordered categories
• Thinking line charts show raw data tables

2. Which of the following is the correct way to set up a line chart in Power BI?

easy

A. Place numeric values on the X-axis and dates on the Y-axis

B. Place a date or ordered category on the X-axis and numeric values on the Y-axis

C. Use only text categories on both axes

D. Use a pie chart visual instead of a line chart

Solution

1. Step 1: Identify axis roles in line charts

   The X-axis should have dates or ordered categories to show progression, and the Y-axis should have numeric values to show changes.

2. Step 2: Eliminate incorrect setups

   Numeric values on X-axis or text-only axes do not show trends properly; pie charts are a different visual type.

3. Final Answer:

   Place a date or ordered category on the X-axis and numeric values on the Y-axis -> Option B

4. Quick Check:

   X-axis = date/order, Y-axis = values [OK]

Hint: X-axis = time/order, Y-axis = numbers for line charts [OK]

Common Mistakes:

• Swapping X and Y axis roles
• Using text categories on both axes
• Choosing wrong visual type

3. Given this data for sales over months: January=100, February=150, March=120, what will the line chart show for March compared to February?

medium

A. A point lower than February

B. A point higher than February

C. A point equal to February

D. No point for March

Solution

1. Step 1: Compare March and February sales values

   March sales are 120, which is less than February's 150.

2. Step 2: Understand line chart plotting

   The line chart plots points on the Y-axis by value; March's point will be lower than February's.

3. Final Answer:

   A point lower than February -> Option A

4. Quick Check:

   120 < 150 means March point lower [OK]

Hint: Lower value means lower point on line chart [OK]

Common Mistakes:

• Assuming March is higher than February
• Confusing equal values
• Thinking March data is missing

4. You created a line chart but it shows all points as a flat line at zero. What is the most likely cause?

medium

A. The data has negative values only

B. The date field is on the Y-axis instead of X-axis

C. The chart type is set to pie chart by mistake

D. The numeric values field is missing or contains zeros

Solution

1. Step 1: Check numeric values field in the chart

   If the values field is missing or all zeros, the line will be flat at zero.

2. Step 2: Verify axis and chart type

   Having date on Y-axis or wrong chart type would cause different errors, not flat zero line; negative values still plot below zero.

3. Final Answer:

   The numeric values field is missing or contains zeros -> Option D

4. Quick Check:

   Missing or zero values cause flat line [OK]

Hint: Check if values field is empty or zero-filled [OK]

Common Mistakes:

• Ignoring missing values field
• Confusing axis placement errors
• Assuming negative values cause flat line

5. You want to compare sales trends for two products over the same months using a line chart. What is the best way to set this up in Power BI?

hard

A. Place months on X-axis, sales on Y-axis, and use product as Legend to show separate lines

B. Place sales on X-axis, months on Y-axis, and filter by product one at a time

C. Create two separate line charts, one for each product, and place side by side

D. Use a pie chart to compare sales of both products

Solution

1. Step 1: Understand how to show multiple lines in one chart

   Using the Legend field with product categories creates separate lines for each product on the same chart.

2. Step 2: Confirm axis roles for trend comparison

   Months go on X-axis to show time order, sales on Y-axis to show values, and product in Legend to differentiate lines.

3. Final Answer:

   Place months on X-axis, sales on Y-axis, and use product as Legend to show separate lines -> Option A

4. Quick Check:

   Legend = product for multiple lines [OK]

Hint: Use Legend for categories to show multiple lines [OK]

Common Mistakes:

• Swapping X and Y axes
• Using separate charts instead of Legend
• Choosing wrong chart type