My Take on Waterfall Chart Visual in Power BI

[Pragati11]

Power BI gives us multiple visualisation options by extending its capabilities to represent the data in multiple ways. Representing same data with different visuals can give audience many ways of seeing the data. This sometimes help us in finding different insights and stories within the data. Today in this blog I will be talking about Waterfall Chart visual and see in what scenarios this chart can be helpful in uncovering insights.

A detailed description about this chart can be found on official Microsoft’s website: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-waterfall-charts

After referring to Microsoft’s documentation, based on my understanding this chart can be used in the following scenarios:

• If we are interested in seeing the change over months for a metric like sales.
• How the change in sales has been overtime – whether it increased or decreased.
• To see this change further broken down by a category for every month – like how the sales of clothing in different clothing categories has been over time?

Let us see if we can get the above insights using in this chart and see if we can find answer to the additional question:

• Which sales categories are profitable – like which clothing category has generated continuous increasing profit for the company?

Let us start with a sample data in which we have sales over time for different clothing categories. See below sample rows:

For a start I will create a simple bar chart that shows me total sales over time (just considering Year = 2016 for this example):

The above chart gives a view on what were the total sales for every month in year 2016. I further want to see these sales for different clothing categories as well. So, we move Category field to the Legend section and get the following chart:

So far so good. I see a breakdown for every clothing category as a sub-split for every month. To make the chart little clearer, we can add a total sale for every month as a line on the above chart just to help with overall monthly comparison with the categorical breakdown:

To answer the above questions, one way is we can create DAX calculations to get the sales change for every month and can visualise them to show if they have increased or decreased over time. I created 2 DAX measure calculations to achieve this in my scenario.

Previous Month Sales =

    CALCULATE(

        SUM(ClothingSales[Sales])

        ,PREVIOUSMONTH(

            ClothingSales[Date]

        )

    )

Sales Difference =

    VAR CurrentMonthSales = SUM(ClothingSales[Sales])

    VAR PreviousMonthSales = [Previous Month Sales]

    VAR Result = CurrentMonthSales - PreviousMonthSales

    RETURN

        Result

Now if I move these measures to a table visual and see how the sales change has been over time, I get the following:

Now what if I do move the above sales difference calculation to waterfall chart:

I can see how my sales difference have been for every month – whether it has increased as compared to the previous month or decreased. Now let us just try to move our total monthly sales to waterfall chart and see what it gives:

Now what I see here is the chart shows me a continuous increase in the sales value without showing any decrease. At this point one thing to note is waterfall chart shows RUNNING TOTAL based increase or decrease. In March 2016 total sales = £1,034, but the chart shows an increase from March to April though total sales in April = £849. So, running total here will be £1,034 + £849. Therefore, this value will always be an increasing value. But what happens when we move our Clothing category in the BREAKDOWN section of the chart:

The chart now looks more sensible. For every month, I can see the change in sales for every clothing category when compared to the next month. For e.g., Jeans had an increase of £135 in February 2016 when compared to January 2016. Ok this information helps. But this still does not tell me which is the most profitable clothing category.  Let us play a little bit with DAX again and if I now add a running total calculation using DAX and try to represent it on a chart to find out the most profitable one:

Running total measure =

CALCULATE(

    SUM(ClothingSales[Sales]),

    FILTER(

        ALLSELECTED(ClothingSales), ClothingSales[Date] <= MAX(ClothingSales[Date])

        && ClothingSales[Category] <= MAX(ClothingSales[Category])

        )

        )

The above chart shows me that SHIRTS seem to be most profitable category based on the sales between January 2016 and June 2016. So yes, I have answer to my question using a simple bar chart with Small Multiples functionality.

Let us summarise our conclusions with respect to Waterfall Chart:

Pros:

• This chart did help us in understanding the change in sales over time when broken down by a category.
• It did help us visualise the Increase & Decrease of this change without even defining some conditional formatting rules as applicable to other charts.
• It did help us in seeing the running totals over months.
• The visual part of the chart looks impressive and can add beauty to dashboards.

Cons:

• The chart always shows the running totals. Could be difficult to understand when using it for the first time.
• The chart did not help us in getting answer to our question regarding most profitable clothing category.

As we know Power BI gives multiple options for visualisations, but sometimes there can be limitations to some of them. This thus leads a developer to stick to basic charts like bar chart, line chart, table visual, etc.

Very much interested to know what your view on this chart is and hopefully this simple usecase helps everyone in understanding what this chart is capable of doing.

- Pragati