Waterfall charts in Power BI

Статия
03/19/2024

APPLIES TO: Power BI Desktop Power BI service

Waterfall charts show a running total as Power BI adds and subtracts values. These charts are useful for understanding how an initial value (like net income) is affected by a series of positive and negative changes.

Each measure of change is a column on the chart. The columns are color coded so you can quickly notice increases and decreases across the data.

The initial and final value columns are often configured to always start from the horizontal axis. The intermediate values are floating columns. A starting point for an intermediate column can be on the horizontal axis or on another axis parallel to the main axis.

The position of the intermediate columns can fluctuate between the initial and final values. The resulting view creates a picture similar to a concave or convex wave or a random waterfall cascade. Waterfall charts are also called bridge charts.

Screenshot that shows an example of a waterfall chart in Power BI.

When to use waterfall charts

Waterfall charts are a great choice for many scenarios:

Represent changes for a measure across time, a series, or different categories.
Audit major changes that contribute to a total value.
Plot your company's annual profit by showing various sources of revenue and arrive at the total profit (or loss).
Illustrate the beginning and ending headcount for your company in a year.
Visualize how much money you earn and spend each month, and the running balance for your account.

Note

If you want to share your report with a colleague, you both need to have individual Power BI Pro licenses. If both parties don't have individual Power BI Pro licenses, the report needs to be saved in Premium capacity to enable sharing. For more information, see sharing reports.

When you want to visualize the steps and relationships of business processes.
When you want to monitor and control data quality.
When you want to visualize and track the progress of project steps.
To analyze data defects and identify their causes.
To understand the workings of the organization and the connections between departments.

Prerequisites

Review the following prerequisites for using waterfall charts in Power BI Desktop or the Power BI service.

Power BI Desktop
Power BI service

This tutorial uses the Retail Analysis Sample PBIX file.

Download the Retail Analysis Sample PBIX file to your desktop.
In Power BI Desktop, select File > Open report.
Browse to and select the Retail Analysis Sample PBIX file, and then select Open.

The Retail Analysis Sample PBIX file opens in report view.
At the bottom, select the green plus symbol to add a new page to the report.

This tutorial uses the Retail Analysis Sample in the Power BI service.

Sign in to the Power BI service (app.powerbi.com).
Select Learn in the navigation pane.
On the Learning center page, under Sample reports, locate and select the Retail Analysis Sample to download it. The sample opens in report view.
At the top, select Edit to open the Report editor.
At the bottom, select the green plus symbol to add a new page to the report.

Create a waterfall chart

The following steps create a waterfall chart to display sales variance (estimated sales versus actual sales) by month.

On the Data pane, expand Sales and select the Total Sales Variance checkbox. By default, Power BI presents the data as a clustered column chart.

This action configures the Total Sales Variance data as the Y-axis for the chart on the Visualizations pane.
To convert the visualization into a waterfall chart, select Waterfall chart on the Visualizations pane.

This action exposes the Category and Breakdown sections on the Visualizations pane.
On the Data pane, expand Time and select the FiscalMonth checkbox.

Power BI updates the waterfall chart with the data in the FiscalMonth category. The initial view of the category data shows the values in ascending order.

Sort the waterfall chart

When Power BI creates the waterfall chart, the data is displayed in ascending or chronological order for the category. In our example, the data is sorted by month in ascending order, January to August, for the FiscalMonth category.

You can change the sort order to view different perspectives of the data.

On the Total Sales Variance chart, select More options (...) > Sort axis > FiscalMonth.

This action changes the sort order of the FiscalMonth category to descending by month. Notice that August has the largest variance and January has the smallest variance.
Open the More options (...) > Sort axis menu.

Notice the checkmark next to FiscalMonth and Sort descending. A checkmark appears next to options represented in the chart visualization.
On the More options (...) > Sort axis menu, select Total Sales Variance.

This action changes the sort from the FiscalMonth category to the Total Sales Variance. The chart updates to show the Total Sales Variance data in descending order. In this view, the month of March has the largest positive variance and July has the largest negative variance.
On the More options (...) > Sort axis menu, change the sort back to FiscalMonth and Sort ascending.

Explore the waterfall chart

Let's take a closer look at the data to see what's contributing most to the changes from month to month.

On the Data pane, expand Store and select the Territory checkbox.

This action adds a corresponding Breakdown field on the Visualizations pane.
Expand the width of the waterfall chart to see more of the data.

Power BI uses the Territory value in the Breakdown section to add more data to the visualization. The chart now includes the top five contributors to increases or decreases for each fiscal month. Notice the month of February now has six data points instead of only one.

Let's say you're only interested in the top two contributors. You can configure the chart to highlight that information.
On the Visualizations > Format visual pane, select Breakdown, and set the Maximum breakdowns value to 2.

The updated chart reveals Ohio (OH) and Pennsylvania (PA) as the top two territories that are the largest contributors to increases and decreases.