How to Setup the Waterfall Chart in Power BI

Waterfall Chart

In this article, I wanted to touch on a visualization that was part of the initial release of Power BI, but one I seldom see on Dashboards or requested by Business Users: The Waterfall Chart. What is it? How do I set it up? What is it useful at displaying? How does it differ from a simple Column chart? We’ll answer those questions below.

What is the Waterfall chart?

A Waterfall chart is a column type chart that shows aggregated data over time. It is useful for displaying the individual components of a measure and how they continually contribute to the overall number. Something like ‘Variance to Goal’ would make good use of a Waterfall chart. I’ll explain more of the features as we explore and set up a simple example.

For this exercise we’ll make up some data in the Power BI Desktop designer. Click the Enter Data button on the Home ribbon and enter values and Column Headers as follows:

Waterfall Data

After clicking OK and loading the table, format the Date column as “(MMMM, yyyy)” and the other two as your favorite currency. That’s it. That’s all we need to move on to some charting. And first up will be a Clustered Column chart showing both Sales and Sales Goal side-by-side for the 12 months:

Sales Goal Column Chart

Because the default for a Column chart is to show the full height, this particular comparison, where the variance between the two numbers is relatively small compared to the total height, the eyes may have a hard time differentiating the columns. Also, we have to work hard to find the four months where the Sales value was below the Goal.

I see this type of visualization often and every time I think, “There’s got to be a better way to show this.” Fortunately, there is, but we need to add a variance calculation. Let’s add a Calculated Column to the table with the following formula:

Sales Goal Variance = [Sales] – [Sales Goal]

This should also be formatted as currency. Charting this value in a simple Column chart will give us a little more insight into how the variance has changed over the course of those same 12 months:

Sales Goal Variance Column Chart

This is a little easier to understand, particularly when it comes to determining when the Variance is negative or positive. But it still lacks the ability to show at what point, for example, we recovered from the shortfall in January and broke even, or if we are above or below our Goal for the entire year.

As useful as this plot is, there is an even better way. Here’s what the Waterfall chart would do with these same data points:

  1. Plot negative numbers in a different color than positive ones.
  2. Start each succeeding month at the point where the prior month ended.
  3. Show a total (positive or negative) for the entire time frame.

For example, if we could slide down the February column so that it started at the end of January’s ($2K),

Waterfall 1

then slide March’s $500 down so it starts where February ended  at ($1k) ( = ($2K) at the end of January + $1K added by February ) and so on, the result would be a Waterfall chart as show here:

Waterfall ChartThe resulting plot is an easy to understand column-type chart showing how each monthly segment contributed to the overall value for the total period. From this chart, we can easily pick out the following tidbits from our data that were not readily available in either of the two previous chart styles:

  • January was disastrous we knew, but by the end of April we had recovered and were back on track.
  • We had four consecutive months of Sales greater than the corresponding monthly Goal (February through May), then settled into a back-and-forth for most of the rest of the year.
  • November was just as bad as January, and brought us slightly behind the Goal for the year.
  • December’s gain was the best, and brought us over our Goal by a wide margin.

How do I set up a Waterfall Chart?

The Waterfall chart is one of the easiest in Power BI to set up. There are three very basic steps, as outlined below, and which need not be done in the order specified.

Select Waterfall chart

  • Select the Waterfall chart from the Visualizations palette. If you have not already added any other elements, it will add a blank chart to the report canvas.
  • Select the Y Axis. In our case, we used a Calculated Column called Sales Goal Variance, which, being a numeric data type, was automatically summed by default. We could have just as easily selected a Measure that already had the appropriate aggregation defined.
  • Select the Category. Typically, a date type is used here, and be careful after selecting as Power BI tends to create Date Hierarchies which must then be manipulated. For this exercise, since my data was already at a monthly grain, I by-passed the hierarchies and went straight for the Date column.

select Date

There are some useful format options, too, that should be noted. The most prominent one is the Sentiment Colors option. With this, the designer can assign colors to the Increase, Decrease and Total bars if, for example, the data was such that a DECREASE was favored over INCREASE.

Sentiment Colors

How does a Waterfall chart differ from a Column chart?

As shown, the two are very close in layout, but there are some major differences:

  • The Waterfall chart cannot show a Series, such as Territory or Product, as either stacked or clustered, like a Column chart can. It is limited to one value only.
  • It does not allow superimposing of a line with a secondary Y axis that shares the same X axis.
  • It can only be displayed with vertical columns and does not allow horizontal bars.

What is a Waterfall chart useful for?

Limitations above aside, the Waterfall chart is still quite useful in the right situation and with the right data. As already shown, when plotting a variance, numbers where there is a potential for negative values, the Waterfall chart excels. It gives somewhat of a Year-to-Date look and feel of the data without the need to write the DAX expression. Note that the ending point for each month (the top of each green column and the bottom of each red column) represents the point of YTD Variance for that month. (I sometimes refer to a Waterfall chart as a ‘poor man’s YTD’.)

Consider the following Column chart that plots the DAX Measure:

YTD Variance = TOTALYTD(SUM(‘Table1′[Sales Goal Variance]),’Table1′[Date])

YTD Variance

This plot is, I believe somewhat misleading to the viewer, even though the ENDS of each monthly column correspond with the ENDS of the columns in the Waterfall. All we can tell for any one month is where we are in YTD Variance, but not how we got there. To arrive at July’s YTD Variance value of $750, for example, we had to DROP from June’s point of $1,750, something that the Waterfall chart clearly indicates, but something we need to derive in the above plot based on the position of July’s end point relative to June’s. Our Sales Goal Variance was NEGATIVE for July, but the above plot seems to indicate that July was POSITIVE!

And lastly, YTD plots always end at the end of the year because, well, that’s in their definition. A Waterfall chart can span multiple years with ease, or even be sliced or filtered to start at a different point along the X (Category) axis. The plot below charts the same data, but shows April through October.

April Waterfall

Hopefully, now that you understand the benefits and limitations of the Waterfall chart, you’ll be more inclined to introduce it and explain its benefits to Business Users for displaying variance data.

Learn more about Blum Shapiro Technology Consulting

Also by this author: KPI’s in Power BI, Not as Hard as You Think.

5 Warning Signs Your Company Has Outgrown QuickBooks

Cloud accounting image

If you’re a small business, you’re most likely running QuickBooks. In fact, millions of businesses use QuickBooks as their very first accounting system. Why?

When companies are starting out, they can’t afford to make huge investments in finance systems. They spend $100 dollars or so and they are all set. They don’t have to invest in training and can be up and running in a matter of days.

For a lot of businesses, QuickBooks is the last accounting system they will ever have to buy because their needs don’t change.

What if Your Business is Growing?

But if your business is growing and evolving, your finance system needs to adapt. Your reporting requirements evolve. More and more people depend on critical reports and financials and need them quicker.

Most businesses respond to these increased expectations by compensating with manual entries, workarounds and the biggest crutch of all…spreadsheets.

As they continue to evolve, they add more workarounds, and more manual, duplicate data entry and more band aids. Now they are so in the weeds trying to get basic financials issued each month that they lose sight of how difficult their life has become each month.

The Key Warning Signs You Have Outgrown QuickBooks

Based on my experience with hundreds of companies, I would like to share with you 5 Warning Signs That Your Company Has Outgrown QuickBooks. We’ll cover the first two warning signs in this post and discuss the last three in our next post.

Warning Sign #1 – Monthly Reporting Nightmares
QuickBooks designed its reporting capabilities for very small businesses. In order to get more complex, meaningful financial reports, all of the data must be exported to Excel, summarized, formatted and printed manually each month.

Change a single amount and you need to repeat the entire process again.

How much time are you wasting taking all of these extra steps each month? What more productive and valuable activities could you be working on instead?

Warning Sign #2 – Disconnected Critical Systems
Let’s face it, as your company grows, your systems become more complex.

You need more systems to manage the operational aspects of your business: customers, time and billing, sales, orders, production, payroll, scheduling, delivery and fulfillment.

QuickBooks was designed to stand alone, creating disconnected silos of information that are very difficult to manage as you grow.

If you are only entering your sales data once a month from your billing system, it’s hard to have any visibility into operations during the month.

Want to Learn More?

If you would like to learn more, we have compiled a free e-Book for you. Simply click here to download.

Please see the original post on the Cloud Accounting Blog >>

7 Reasons You Should Consider Outsourcing Your Finance and Accounting

Cloud Accounting Blog Logo

Successful businesses, no matter how small or large, are able to focus, like a laser beam, on what’s important: innovation, customer service, growth, company culture and winning against their competition.

Finance and accounting, while critical to business operations, is often urgent, but not important. Yet so many executives like you allow themselves to get distracted from these critical success factors by responding to inquiries, tracking down missing checks, making sure the books get closed accurately and reconciling accounts.

If you are looking for better ways to focus on growing your business you should consider outsourcing your finance and accounting operations. Here are seven key benefits of outsourcing accounting and finance.

  1. Be more efficient– For one week, keep track of how you spend your day. (You can easily accomplish this with a free tool called toggl. http://toggl.com ) How much time do you spend each week on finance and accounting? If it is more than 1 – 2 hours a week…it’s too much. You should be spending your time improving operations, better serving customers and growing your business. Removing the daily distractions of accounting will help you do this.
  2. Reduce costs – Outsourcing your accounting eliminates all of the costly taxes and fringe benefits associated with full and part time employees. You pay one fixed monthly fee for everything. Research has shown that outsourcing accounting can save up to 40% in monthly costs, when you consider the salary plus taxes, supervision, vacation and health insurance.
  3. Eliminate fraud –Most small businesses have one accounting person that does everything….sends out the bills, collects and deposit checks and reconciled the bank account. When these duties are not separated, you increase your risk of fraud. A recent Association of Certified Fraud Examiner’s study showed that the most common victims of fraud are privately owned small businesses with less than 100 employees with an average fraud amount of $147,000. Outsourced accounting provides you with the checks and balances, as well as the oversight that you need to prevent fraud.
  4. Highly qualified and experienced staff – By having a team of accountants and CPAs work together to take care of your books, you can take advantage of their significant accounting, tax and compliance expertise which is all included in the monthly cost. By outsourcing you will automatically stay ahead of and comply with the myriad changes in income and sales tax and reporting laws.
  5. Ability to scale – By outsourcing finance and accounting, scaling your business becomes easier. Rather than distract yourself by hiring additional finance staff, outsourcing grows automatically with your business. You can focus on hiring the best people to sell your products and service your customers…which goes right to the bottom line.
  6. Improve cash flow – Outsourcing provides you with access to cloud based tools and technologies that will help you get paid faster and manage payments more effectively. At the simple click of a mouse, you can see an up to minute analysis of your cash.
  7. Better Manage Your Business –What type of information are you receiving today from your finance system? Most importantly, how timely is it? When you get last month’s financial on the 20th of the following month, how do you support decisions in the beginning of the month? Outsourcing provides you with real time information on all aspects of your business, not just financials, with the click of a mouse.
Can your company benefit from outsourcing your finance and accounting? Take our 1 minute assessment! Get your assessment now >>

Automating VM Startup and Shutdown in Azure Resource Manager

This article summarizes the differences in automating starting up and shutting down virtual machines (VMs) between Azure Resource Manager and Classic mode.

Microsoft introduced Azure Resource Manager to facilitate management of your cloud assets in Azure, but given how new it is, it is often difficult to find exactly how to do that.

Earlier this year I created a new Azure VM for a client for use during business hours. The client requested the ability to automate the startup and shutdown of the VM when not in use, in order to save on costly compute time. Implementing a scheduled startup and shutdown of classic mode VMs has been documented and posted to various blogs. It was hard to find how to schedule startup and shutdown when the VMs are managed in the new Azure Resource Manager (ARM).

Classic method

The classic method of setting up auto-start up and shut down involves three steps: create an automation account and configure automation credentials, create runbooks that contain the PowerShell workflow scripts you want to execute, and attach one or more schedules to those runbooks. With Azure Resource Manager, the PowerShell workflow script you use in the second step is different.

In the classic method, you would use code similar to the following to set up your automation.

Startup script

workflow Start_MyClassicVM

{

$cred = Get-AutomationPSCredential -Name “MyAutomationCredential”

Add-AzureAccount -Credential $cred

Select-AzureSubscription “MySubscription”

$myVM = “MyVMName”

$serviceName = “MyService”

Start-AzureVM -ServiceName $serviceName -Name $myVM

}

First you fetch your credentials for your automation account and authenticate using the Add-AzureAccount method.

Once authenticated you designate which subscription context you are using. Often this might be called “Pay-As-You-Go” or “MSDN Subscription”.

You would then specify your service and run the Start-AzureVM command against your VM in that service.

Azure Resource Manager method

When using Azure Resource Manager you would adapt the following code.

Startup script

workflow Start_MyRmVM

{

$cred = Get-AutomationPSCredential -Name “AzureServiceAccount”

Add-AzureRMAccount -Credential $cred

Get-AzureRmSubscription -SubscriptionName “MySubscription” -TenantId “GUID-goes-here” | Set-AzureRmContext

$resourceGroup = “MyResourceGroup”

$myVM = “MyRmVm”

Start-AzureRmVM -ResourceGroupName $resourceGroup -Name $myVM

}

While configuring the automation account, runbooks and schedules remain the same, there are several key differences between automating startup of an ARM VM and a classic VM. Almost all of the methods you use with the Azure Resource Manager will contain “Rm” after the word “Azure” in their name. Also, the statement starting with “Get-AzureRmSubscription” is a bit more complex than in the classic version. Note that now, you need to know not just your subscription name, but also your Azure Active Directory Tenant ID. (Since I did not have access to the Azure Active Directory for my client’s subscription, I retrieved this GUID from the URL in the address bar after navigating to the Azure Active Directory in the classic portal. It’s possible, however, that this will change in the near future.) You then pipe the results of this new command, complete with the Tenant ID, to Set-AzureRmContext.

After that, rather than supplying a Service Name, you supply the name of your Azure Resource Group to the Start-AzureRmVM cmdlet.

Azure menu

Active directory link in Azure Portal menu

Shutdown script

To shut the VM down, the code is near-identical except for the last line:

Stop-AzureRmVM -ResourceGroupName $resourceGroup -Name $myVM -Force

Here is the full workflow script for shutdown.

workflow Stop_MyRmVM

{

$cred = Get-AutomationPSCredential -Name “AzureServiceAccount”

Add-AzureRMAccount -Credential $cred

Get-AzureRmSubscription -SubscriptionName ‘Pay-As-You-Go’ -TenantId “GUID”| Set-AzureRmContext

$resourceGroup = “MyResourceGroup”

$myVM = “MyRmVM”

Stop-AzureRmVM -ResourceGroupName $resourceGroup -Name $myVM -Force

}