How to Combine Two Line Charts in Power BI

Combining two line charts into a single visual in Power BI is the perfect way to compare trends and spot correlations between different metrics. Instead of making your audience shift their eyes between two separate graphs, you can place the trends side-by-side on a shared timeline. This guide will walk you through the primary methods for combining line charts and offer best practices for making your visuals clear and insightful.

Why Combine Two Line Charts in the First Place?

The primary goal of any dashboard is to communicate information quickly and effectively. Merging two line charts into one visual helps you do exactly that by providing context. When two metrics are displayed together, their relationship (or lack thereof) becomes immediately obvious.

Consider these common business scenarios:

• Sales vs. Marketing Spend: Are your marketing campaigns actually lifting sales? By plotting marketing spend and sales revenue on the same timeline, you can see if spikes in ad spend correspond with increases in sales.
• Website Traffic vs. Conversion Rate: You're getting more visitors, but are they the right visitors? Laying traffic data over your conversion rate can show if a surge in users is leading to a higher or lower percentage of conversions, informing you about the quality of that traffic.
• Actual Revenue vs. Forecasted Revenue: Are you on track to hit your goals? A combined chart clearly shows the gap between your plan and your reality, month by month, helping you adjust your strategy.
• Customer Support Tickets vs. New Feature Launches: Did that new software update cause a flood of support requests? Plotting these two lines together can help your product and support teams understand the impact of new releases.

In all these cases, the combined chart doesn't just show two pieces of data, it tells a story about how different parts of your business influence each other.

Data Preparation: The Foundation of a Great Chart

Before you even open the visualizations pane in Power BI, it's critical to ensure your data is structured properly. A poorly prepared dataset will lead to confusing charts and incorrect insights. For a combined line chart, you have one simple but non-negotiable requirement:

Both metrics must share a common axis.

Most of the time, this shared axis will be a time-based dimension, like a date, week, or month. Your data table should look something like this:

Key Preparation Steps:

1. Verify Data Types: Ensure your date column is formatted as a Date type in Power BI. You can check and change this in the Power Query Editor or the Data view. Numerical columns should be set to a numerical type like Decimal Number or Whole Number.
2. Create a Calendar Table: For more robust time intelligence, using a dedicated Calendar table is best practice in Power BI. You can create one easily using DAX (e.g., Calendar = CALENDARAUTO()) and connect it to your main data table via the date field. This ensures you have a continuous date range with no gaps, which is essential for accurate line charts.

Method 1: Using the Secondary Y-Axis (Most Common)

This is the quickest and most direct way to combine two line charts, especially when your two metrics have vastly different scales. Imagine trying to plot a conversion rate (e.g., 3.5%) on the same axis as website visitors (e.g., 50,000). The conversion rate line would appear completely flat and useless. The secondary Y-axis solves this problem.

Here’s the step-by-step process:

Step 1: Create Your Initial Line Chart

First, add the standard Line chart visual to your report canvas. Then, drag your shared axis (e.g., Date) into the X-axis field. Drag your first metric (e.g., Sales Revenue) into the Y-axis field. At this point, you should have a single, simple line chart.

Step 2: Add the Second Metric to the Secondary Y-Axis

Now, locate your second metric in the Data pane (e.g., Marketing Spend). Drag this metric directly into the Secondary y-axis field well in the Visualizations pane.

Instantly, a second line will appear on your chart, and Power BI will create a new axis on the right-hand side of the visual, scaled specifically for this second metric. You now have two distinct lines, each with its own properly scaled axis, using the same shared timeline.

Why this method works well:

• Handles Different Scales: It's the ideal solution for comparing metrics with wildly different magnitudes, like revenue in millions versus user count in thousands.
• Fast and Intuitive: It only takes two drag-and-drop actions to set up.
• Clear and Readable: It's easy for viewers to understand that there are two separate scales by looking at the axes on the left and right sides.

Method 2: Unpivoting Data for Maximum Flexibility

What if your metrics have similar scales and you want to use a slicer to let users dynamically choose which lines to display on the chart? For this, a data transformation technique called "unpivoting" is a more powerful approach.

This method transforms your data from a "wide" format (multiple columns for metrics) to a "long" format (one column for metric names and one column for their values). It sounds complex, but it's straightforward in Power Query.

Step 1: Open Power Query and Select Metric Columns

From the main Power BI window, click on Transform data to open the Power Query Editor. Once loaded, click to select the first metric column (e.g., Sales Revenue), then hold down the Ctrl key and click to select your other metric columns (e.g., Marketing Spend). You can select as many as you'd like to compare.

Step 2: Unpivot the Selected Columns

With your metric columns selected, navigate to the Transform tab in the ribbon. Find the Unpivot Columns button and click it. Two new columns will appear, typically named "Attribute" and "Value".

• The "Attribute" column contains the former column headers (e.g., "Sales Revenue," "Marketing Spend").
• The "Value" column contains the corresponding numeric values for each metric on a given date.

You should rename these new columns for clarity. A good practice is to rename "Attribute" to "Metric Name" and "Value" to something like "Amount" or just "Value."

Initial "Wide" Data:

Transformed "Long" Data after Unpivot:

Step 3: Build the Chart with Unpivoted Data

After clicking Close & Apply in Power Query, return to the report view and create a new Line chart. This time, the setup is different:

1. Drag Date to the X-axis.
2. Drag Value (your numeric value column) to the Y-axis.
3. Drag Metric Name (the new attribute column) to the Legend field.

Power BI will automatically generate a separate, color-coded line for each unique item in your "Metric Name" column — all on the same chart and sharing the same Y-axis.

Pros and Cons of the Unpivot Method:

• Pro - Ultra Flexible: You can add a Slicer visual tied to the Metric Name field. This lets users interactively turn lines on and off in the chart, perfect for dashboards with many comparable metrics.
• Pro - Scalable: If you need to add a third or fourth metric later, you don't even have to touch the chart itself. You just modify the unpivot step in Power Query to include the new column.
• Con - Shared Axis Only: This method is not suitable for metrics with different scales, as everything is plotted against the single primary Y-axis.

Formatting Tips for Clean and Readable Combined Charts

Now that your chart is built, the final step is formatting it to ensure the story is easy to tell.

• Use Clear Axis Titles: By default, Power BI might use titles like "Sum of Sales Revenue." Go to the Format your visual pane, find the Y-axis sections (there will be one for primary and secondary), and rename the titles to something simple and clear like "Total Sales ($)" and "Marketing Spend ($)." This removes ambiguity.
• Choose Contrasting Colors: Navigate to Lines > Colors to manually set the color for each line. Pick colors that are visually distinct and avoid combinations that may be difficult for colorblind individuals to differentiate (e.g., red and green).
• Vary Line Styles: To add another layer of visual separation, go to the Lines > Shape section. You can change the Stroke style of one of the lines to be Dashed or Dotted to make it stand out even more.
• Enable a Legend: Make sure the Legend is turned on and positioned in a logical place (top or bottom usually works well). This explicitly tells the viewer which line represents which metric.
• Add Markers and Labels (Sparingly): Under the Markers section, you can add shapes (circles, squares) to the data points on each line to make them easier to see. Be cautious with Data labels, they can add precision but quickly clutter the chart if you have many data points. A clean chart is often better than a crowded one.

Final Thoughts

Mastering both the secondary Y-axis and unpivot methods gives you the flexibility to handle practically any scenario where you need to compare trends in Power BI. The key is to choose the right approach based on your data scales and whether you need dynamic, user-driven filtering.

While building rich, interactive visuals in Power BI is a fantastic skill, we know the hours it takes can slow you down from getting to the actual insight. At Graphed, we felt that same friction, which is why we built a tool to turn that manual process into a simple conversation. Instead of dragging and dropping fields, configuring axes, and managing data models, you can just ask, "Show me a chart comparing sales and ad spend for this quarter," and we instantly create the interactive dashboard you need, connected live to your data.