Often you may want to create a line chart in Power BI that shows the cumulative values of some variable over time.
The following step-by-step example shows how to create the following cumulative line chart in Power BI:
Let’s jump in!
Example: How to Create a Cumulative Line Chart in Power BI
Suppose we have the following table in Power BI named my_data that contains information about sales made on various dates by some company:
Suppose we would like to create a line chart that displays the cumulative sales over time.
Before we can create the line chart, we must add a new column that contains the cumulative sum of the values in the Sales column.
To do so, click the Table tools tab along the top ribbon, then click the New column icon:
Then type the following formula into the formula bar:
Cumulative Sales = CALCULATE ( SUM ( 'my_data'[Sales] ), ALL ( 'my_data' ), 'my_data'[Date] <= EARLIER ( 'my_data'[Date] ) )
This will create a new column named Cumulative Sales that contains the cumulative sum of the values in the Sales column:
To create a line chart that displays the values in the Cumulative Sales column, click the Report View icon on the left hand side of the screen:
Then, on the right side of the screen under Visualizations, click the Line chart icon:
An empty line chart will be inserted:
Next, drag the Date variable under the X-axis label, then drag the Cumulative Sales variable under the Y-axis label:
This will produce the following line chart:
The x-axis displays the date and the y-axis displays the cumulative sales.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI:
Power BI: How to Create a Line Chart by Month
Power BI: How to Add Trend Line to Line Chart
Power BI: How to Plot Multiple Lines on Line Chart
how about it is not by date? it is by price, from low to high? thanks
Hey!
How is it possible to make in a way that the years are summed up separately and also represented on separate lines?
Thanks in advance!
Hi Fanni…To create a **Cumulative Line Chart** in Power BI where the years are summed up separately and represented on separate lines, follow these steps:
—
### **Step 1: Prepare the Data**
Ensure your dataset includes the following columns:
– **Date**: Contains the date of the data.
– **Year**: Extract the year from the date (if not already present). You can do this in Power BI by creating a calculated column:
“`DAX
Year = YEAR(‘Table'[Date])
“`
– **Value**: Contains the numeric values to be aggregated cumulatively.
—
### **Step 2: Create a Cumulative Total Measure**
Create a **DAX measure** to calculate the cumulative total for your data. This ensures that the values are summed progressively over time.
1. Go to the “Modeling” tab and click on **New Measure**.
2. Use the following DAX formula:
“`DAX
Cumulative Total =
CALCULATE(
SUM(‘Table'[Value]),
FILTER(
ALL(‘Table’),
‘Table'[Year] = MAX(‘Table'[Year]) && ‘Table'[Date] <= MAX('Table'[Date]) ) ) ``` - `SUM('Table'[Value])`: Sums the values. - `ALL('Table')`: Ensures the filter context is reset to include all rows. - `FILTER`: Restricts the calculation to the same year (`'Table'[Year] = MAX('Table'[Year])`) and dates up to the current one (`'Table'[Date] <= MAX('Table'[Date])`). --- ### **Step 3: Create the Line Chart** 1. Add a **Line Chart** visual to your Power BI canvas. 2. Drag the following fields to the appropriate sections: - **X-Axis**: Drag and drop the `Date` field. (Ensure it is sorted correctly to represent the progression of time.) - **Legend**: Drag the `Year` field to separate the lines by year. - **Values**: Drag the `Cumulative Total` measure you just created. --- ### **Step 4: Format the Chart** 1. Go to the **Format Visual** pane: - Ensure the `X-Axis` is set to show continuous values. - Adjust colors and labels to clearly differentiate the lines for each year. 2. If your dataset spans multiple years, you might want to truncate the `Date` to month and day (ignoring the year) for a cleaner x-axis. To do this: - Create a new calculated column for "Month-Day": ```DAX MonthDay = FORMAT('Table'[Date], "MM-DD") ``` - Replace the `Date` field in the x-axis with this `MonthDay` column. --- ### **Outcome** The resulting chart will: - Plot cumulative totals over time for each year. - Represent each year's cumulative total as a separate line, clearly distinguishing them using the legend. This approach ensures clarity in comparing cumulative trends across different years!
Thank you so much for your help James! It works perfectly.
You are very welcome Fanni! We appreciate your feedback and wish you the best!