Scatter plots in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the relationship between advertising spend and sales revenue across different stores to identify if higher advertising leads to higher sales.

📊 Data: You have monthly data for 10 stores including Store ID, Advertising Spend (in $), Sales Revenue (in $), and Store Size (in square feet).

🎯 Deliverable: Create a scatter plot in Power BI showing Advertising Spend on the X-axis and Sales Revenue on the Y-axis. Use Store Size to adjust the size of the dots to add more insight.

Sample Data

Store ID	Advertising Spend ($)	Sales Revenue ($)	Store Size (sq ft)
Store 1	5000	45000	1500
Store 2	7000	60000	1800
Store 3	3000	28000	1200
Store 4	8000	75000	2000
Store 5	2000	22000	1100
Store 6	6000	58000	1700
Store 7	4000	39000	1400
Store 8	9000	80000	2100
Store 9	3500	32000	1300
Store 10	10000	90000	2200

1

Step 1: Load the sample data into Power BI by importing the table with columns Store ID, Advertising Spend, Sales Revenue, and Store Size.

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the data into Power BI.

Expected Result

Data table with 10 rows and 4 columns loaded into Power BI.

2

Step 2: Create a scatter plot visual on the report canvas.

From the Visualizations pane, select the Scatter chart icon.

Expected Result

An empty scatter plot visual appears on the report canvas.

3

Step 3: Assign data fields to the scatter plot: set 'Advertising Spend' to X-axis, 'Sales Revenue' to Y-axis, and 'Store Size' to Size. Use 'Store ID' as Details to identify each point.

Drag 'Advertising Spend' to X Axis, 'Sales Revenue' to Y Axis, 'Store Size' to Size, and 'Store ID' to Details in the scatter plot fields.

Expected Result

Scatter plot shows 10 dots representing stores, positioned by advertising spend and sales revenue, with dot sizes reflecting store size.

4

Step 4: Format the scatter plot for clarity: add data labels showing Store ID, set axis titles, and adjust colors for good contrast.

In Format pane, turn on Data labels, set X-axis title to 'Advertising Spend ($)', Y-axis title to 'Sales Revenue ($)', and choose a color palette with high contrast.

Expected Result

Scatter plot clearly shows labeled points with readable axis titles and distinct colors.

5

Step 5: Analyze the scatter plot to identify the relationship between advertising spend and sales revenue.

Observe the trend of dots from left to right and bottom to top.

Expected Result

You see that stores with higher advertising spend generally have higher sales revenue, and larger stores tend to have bigger dots.

Final Result

Scatter Plot: Advertising Spend vs Sales Revenue

Y-axis (Sales Revenue $)
|
|          * Store 10 (biggest dot)
|        * Store 8
|       * Store 4
|      * Store 2
|     * Store 6
|    * Store 1
|   * Store 7
|  * Store 9
| * Store 3
|* Store 5
+------------------------------------> X-axis (Advertising Spend $)

✓There is a positive relationship between advertising spend and sales revenue.

✓Stores with higher advertising spend tend to generate more sales.

✓Store size influences the size of the dots, showing bigger stores generally have higher sales.

✓This visualization helps identify which stores might benefit from increased advertising.

Bonus Challenge

Add a trend line to the scatter plot to quantify the relationship between advertising spend and sales revenue.

Show Hint

Use the Analytics pane in Power BI scatter plot visual to add a trend line and observe the slope.

Practice

1. What is the main purpose of a scatter plot in Power BI?

easy

A. To show the relationship between two numeric variables

B. To display data in a pie chart format

C. To list data in rows and columns

D. To create a map visualization

Solution

1. Step 1: Understand scatter plot basics

   A scatter plot uses two numeric axes to show how two variables relate to each other.

2. Step 2: Compare with other chart types

   Pie charts show parts of a whole, tables list data, and maps show locations, not relationships between numbers.

3. Final Answer:

   To show the relationship between two numeric variables -> Option A

4. Quick Check:

   Scatter plot = relationship between numbers [OK]

Hint: Scatter plots always compare two numeric values [OK]

Common Mistakes:

• Confusing scatter plots with pie charts
• Thinking scatter plots show categories only
• Using scatter plots for non-numeric data

2. Which of the following is the correct way to assign data fields in a Power BI scatter plot?

easy

A. Assign date data to Size and text data to X Axis

B. Assign text data to X Axis and Y Axis, numeric data to Legend

C. Assign numeric data to Legend only

D. Assign numeric data to X Axis and Y Axis, optional Legend for categories

Solution

1. Step 1: Identify correct field types for axes

   Scatter plots require numeric data on X and Y axes to plot points in 2D space.

2. Step 2: Understand optional fields

   Legend can be used for categories (text), and Size can represent numeric values for point size.

3. Final Answer:

   Assign numeric data to X Axis and Y Axis, optional Legend for categories -> Option D

4. Quick Check:

   Numeric on axes + Legend for categories [OK]

Hint: X and Y axes must be numeric for scatter plots [OK]

Common Mistakes:

• Putting text data on X or Y axes
• Using Legend for numeric axes
• Assigning date data incorrectly

3. Given a scatter plot with Sales on X Axis and Profit on Y Axis, what pattern would you expect if higher sales generally lead to higher profit?

medium

A. Points are randomly scattered with no pattern

B. Points form a horizontal line

C. Points trend upward from left to right

D. Points trend downward from left to right

Solution

1. Step 1: Understand relationship between Sales and Profit

   If higher sales lead to higher profit, as sales increase (X axis), profit (Y axis) should also increase.

2. Step 2: Visualize the scatter plot pattern

   This creates an upward trend from left (low sales) to right (high sales).

3. Final Answer:

   Points trend upward from left to right -> Option C

4. Quick Check:

   Positive correlation = upward trend [OK]

Hint: Positive correlation means points go up left to right [OK]

Common Mistakes:

• Confusing upward and downward trends
• Expecting horizontal line for correlation
• Ignoring relationship direction

4. You created a scatter plot but all points appear in a single vertical line. What is the most likely cause?

medium

A. X Axis has the same value for all data points

B. Y Axis has the same value for all data points

C. Legend field is missing

D. Size field is assigned text data

Solution

1. Step 1: Analyze vertical line pattern

   A vertical line means all points share the same X coordinate but vary in Y.

2. Step 2: Identify cause in data assignment

   If X Axis data is constant for all points, they align vertically.

3. Final Answer:

   X Axis has the same value for all data points -> Option A

4. Quick Check:

   Same X value = vertical line [OK]

Hint: Same X values cause vertical line in scatter plot [OK]

Common Mistakes:

• Thinking missing Legend causes vertical line
• Confusing vertical with horizontal line causes
• Assigning text to Size field causing this

5. You want to highlight different product categories in a scatter plot showing Sales (X Axis) vs Profit (Y Axis). Which field should you add to best achieve this?

hard

A. Add Product Category to X Axis

B. Add Product Category to Legend

C. Add Product Category to Size

D. Add Product Category to Y Axis

Solution

1. Step 1: Understand how to differentiate categories visually

   Legend assigns colors or markers to categories, making groups visible in scatter plots.

2. Step 2: Evaluate other field assignments

   Adding categories to Size or axes is incorrect because axes require numeric data, and Size is numeric for point size.

3. Final Answer:

   Add Product Category to Legend -> Option B

4. Quick Check:

   Legend = category colors in scatter plot [OK]

Hint: Use Legend for categories to color points [OK]

Common Mistakes:

• Putting categories on numeric axes
• Using Size for categories instead of numeric values
• Not using Legend to differentiate groups