The waterfall chart is one of the most powerful standard visuals in Power BI given its ability to compute and format the variances of individual items between two periods by default. The items representing the largest variances are displayed as columns of varying length, sorted and formatted with either an increase (green) or decrease (red) color. This built-in logic and conditional formatting makes waterfall charts both easy to create and intuitive for users.

In the following example, the Internet Sales of the last two completed months is broken down by Sales Territory Country:

Waterfall chart with breakdown

The waterfall chart naturally walks the user from the starting point category on the left (2017-Oct) to the ending point category on the right (2017-Nov). As per the preceding image, hovering the cursor over a bar results in the details for this item being displayed as a tooltip. In this example, hovering over the ($15K) red bar for the United States displays the Internet Sales for both months, the variance, and the variance as a percentage. These four tooltip values are provided by default and report authors can optionally add measures to the Tooltips field to deliver even greater context.

The Internet Net Sales measure is applied to the y-axis input field, and the Calendar Yr-Mo and Sales Territory Country columns are applied to the Category and Breakdown input fields, respectively. For this visual, the Max breakdowns property available under the Breakdown formatting card is set to 4 and thus only four countries are displayed. The other breakdown item, formatted in yellow by default, is used to summarize the variances for all items not displayed as a breakdown column. In the preceding example, the relatively smaller variances from Australia and Germany are automatically rolled into the other item.

As with other visuals, a Show Data and an Export data option is available in both Power BI Desktop and when viewing the visual in the Power BI service. These options are exposed under the ellipsis (three dots) in the top right corner of each visual. As one example, the user could select Show Data for the waterfall chart to view the sales data for all the countries (including Germany and Australia) in a table format. Report authors can adjust the Max breakdowns property to display greater detail and reduce the size of the other breakdown. However, waterfall charts with fewer breakdown columns are easier for users to interpret.

As per the following image, a filter is applied to the Calendar Month Status column to only include the Prior Calendar Month and the 2 Mo Prior Calendar Month values:

Filter impacting the waterfall chart

This filter results in only two month values being available to the visual and ensures that the visual will update over time. For example, in March of 2018, the visual will automatically update to compare January of 2018 versus February of 2018. This filter can be applied at the report level, page level, or visual level scope, depending on the scenario.

Details on building date dimension columns such as Calendar Month Status into a Power BI dataset are included in the Date dimension view section of Chapter 8, Connecting to Sources and Transforming Data with M. Additionally, filter scopes and the filter conditions available to each scope was reviewed in the Report filter scopes section in Chapter 11, Creating and Formatting Power BI Reports.