Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Power BI Funnels and Waterfalls


By:   |   Updated: 2017-09-18   |   Comments (2)   |   Related: More > Power BI

Problem

What are Power BI Funnels and Waterfalls and how can they best be used?

Solution

Often when you hear the term waterfalls and funnels you may think of a physics exam or maybe some sort of geological effect, but in this case, we are actually referring to a set of charts that are available in Power BI. A funnel chart actually shows you values as you move from one series to the next, in the shape similar to a funnel. A waterfall chart shows you changes or variances over the course of a set of categories. We will cover both of these charts in more detail throughout the rest of this tip.

Power BI Funnel and Waterfall Charts

To get started you will need to download Power BI Desktop from - https://powerbi.microsoft.com/en-us/desktop/. Power BI Desktop is the client application that is available for you to design Power BI Dashboards on a local desktop. Once Power BI desktop is installed we can go ahead and start working with the two chart types. We are going to make use of the Wide World Importers SQL 2016 database for our sample data. You can download the Wide World Importers database from - https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers. In particular, we will be using the WideWorldImportersDW database.

We are now ready to demonstrate some of the details for each of these chart features. Also note that the Power BI Community has several “enhanced versions” of many charts which can be reviewed at - https://store.office.com/..

Power BI Funnel Chart

A funnel chart is designed to show the natural progression of a value over a series; many times, the series is over some time period or set of stages of a project. Generally, the values will adjust, downward or upward, in a consistent sequence to allow the funnel to be uniform, width wise, and narrow somewhat consistently from period to period, stage to stage or series point to series point.  However, the uniformity is not a “hard” requirement. Let us get started with our example.

Our first step is to load several tables from the WideWorldImporters Database; the Fact Sale and Dimension City tables will be utilized for this tip.

Get Data - Description: Get Data screen

Next, the Funnel chart will need to be selected and dragged onto the design grid.

Create Funnel - Description: Create Funnel Screen

Next we add a measure value, Profit from Fact Sale, to the Value area and then add Sales Territory from Dim city to the Group area.

Add values - Description: Add Group and Values items

Now, as shown below, our basic funnel graph is formed.  It shows the series change from largest to smallest of the Sales Territory regions (all in the shape of a funnel).

First look at Funnel - Description: First look at Funnel

However, we can add several features to our basic chart. First, we can go to the format tab, and adjust the data colors used by each of the group categories. As illustrated below, we set a default color, and then each data point in the category, the Sales Territory in our example, can have its own color customized.

Funnel with Data Colors - Description: Funnel with Data Colors

Additionally, we can adjust the data labels and conversion labels font and formatting as shown next. These changes will make it easier to see the raw values.

Data Labels - Description: Data LabelsConversion Rate Labels - Description: Conversion Rate Labels

We can also add fields to the tooltip that appears when hovering over the visual. In the below example, the Tax Amount was added. Notice on the tooltip that other details are included in addition to the value we added. The percent of first and percent of previous are very nifty additions.

Tootip - Description: Add tooltip

Finally, we can add drill down capabilities by adding a second category group, StateProvince in the below screen print.

Additional Group - Description: Additional Group

Now on the upper left corner of the visual we have two drill down buttons; the first, with two down arrows not connected, drills down one full level, whereas the button with two down arrows that are connected does an expand all drill down. 

Dril Down - Description: Drill Down

The results of the expand down one level show the break down by state.

Drill Down 1 Level - Description: Drill Down 1 Level

The Expand All option first shows the Sales Territory and then the State / Province.

Drill Down Expand All - Description: Drill Down Expand All

NNow, let us move on to Waterfalls.

Power BI Waterfall Visualizations

Waterfall charts share some similarities with the funnel chart in that it attempts to show change over some series or category. However, instead of showing total or summated values, a waterfall visualization actually focuses on the variation or cumulative effect between different values. Thus, instead of showing the total sales for a group of years, a waterfall chart will show the difference or variance between sales figures from year to year or category to category. The implication is to show the trend that is occurring between values in the series; it is a great chart to show how you got to a specific ending result.

We will use the same dataset we used for the funnel visualization to show the features of a waterfall chart. Thus, the first step is to add the waterfall visual to the design grid.

Add Waterfall Chart - Description: Add Waterfall Chart

Next, we will use the Profit Month over Month (Profit MoM) measure as the Y axis and then use Month as the Category. You can see in the below example the ups and down of the Profit from month to month.

Add measures to Waterfall Chart - Description: Add measures to Waterfall Chart

We can further refine or breakdown our waterfall by adding a breakdown value. This feature provides us with the “biggest” contributors to the change in value. For the below illustration, we added Sales Territory to the breakdown field, which in turn shows the breakout of each category, the month, by the top 2 Sales Territories plus a summary category of Other.

Add Breakdown - Description: Add Breakdown

We control the number of breakout categories by going to the Format Tab and then scrolling to the Breakdown pane.

Breakdown Threshold - Description: Breakdown Threshold

Additionally, we can change the colors for each of the waterfall bars/columns by using the Sentiment colors pane.

Change bar color - Description: change bar color

One item that you may be asking about is how did I calculate the Month over Month Value? We need to use a formula to create a new measure for that calculation. The calculation is based on the Dimension Date Month field and the Quick Measures option (I hope to do a future tip soon on the Quick Measure which is currently in preview mode). The formula is shown below.

Month over Month Formula - Description: Month over Month Formula

As you can see, these two charts break out of the ordinary bar and line chart mode and provide several key insights in a handy visual format.

Next Steps


Last Updated: 2017-09-18


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, September 18, 2017 - 12:39:13 PM - Scott Murray Back To Top

 

 Jason,

I know there are various opinions on the use and user of funnel charts. In an attempt to show the features, and not show the same old time series, the centered bar charts were a good example. I do agree though the best use is for time series or project progression.  Thanks for your feedback.


Monday, September 18, 2017 - 12:04:31 PM - Jason Hurley Back To Top

These are good writeups and good screenshots of how to do both of these visuals.

I did read your paragraph about Funnels, but it would make more sense to actually do a Funnel of a progression over a time series.  What you displayed was just a centered horizontal bar chart ordered descending by value.  In my experience, I've found graphing Funnels for applicable scenarios are difficult.  It would be of great value to show how to do a real Funnel.


Learn more about SQL Server tools