Introduction

This is test 3 in a series of real-world examples where Tableau capabilities are compared to those of Power BI (PBI). This test should represent the easiest challenge conducted to date.

In this example, I am going to plot time series data that represents a theoretical 37-year history of power generation from a small dam in Lenoir City, TN. This example, like all others, just popped into my head one moment and I thought about doing the test. No pre-conceived notions were resident in my mind before choosing this topic.

You can read about previous work completed with PBI, or inspired from my Power BI experiences, from the list shown below.

Previous Work on The Topic of Power BI, or Inspired by Power BI

1. Impressions From My First 5 Minutes Of Using #PowerBI
2. Impressions From My First Day Of Using #PowerBI
3. Impressions From Our First Month of Using #PowerBI
4. How Microsoft Could Put Real Power into Power BI
5. How To Achieve Better Data Comprehension, Part 1
6. How to Achieve Better Data Comprehension, Part 2
7. Test 1 of Tableau vs Power BI: Topographic Mapping
8. Test 2 of Tableau vs Power BI: Visualizing Surface Water Flow

Technical Background

Figure 1 shows a map of the Tellico dam built in Lenoir City, TN. Figure 2 shows what the actual dam looks like from the nearby road shown in Figure 1. For all graphics in this article, you can click on them to see full-resolution versions.

Figure 1 – The map of the dam.

Figure 2 – A picture of the actual dam.

Rather than writing a lot of technical background information about this study, I made a short video (next section) that describes the project. This project was extracted from the book Living Math, by my friend Dudley Benton, as shown in Figure 3.

Figure 3 – Living Math by Dudley Benton.

In particular, a time series simulation from Chapter 18 was used to generate the data for the visual comparison of Power BI and Tableau graphing capabilities. The book contains the Excel worksheet used to create the model, including the two graphics that this test are intended to reproduce (Figure 4 and Figure 5).

Figure 4 – Chart 1 to be reproduced. This was originally created in Excel by Dudley.

Figure 5 – Chart 2 to be reproduced. This was also initially produced in Excel.

This test seemed like a fair choice for Power BI because all of the original work was completed in Excel. My expectation is that Power BI should have no problem reproducing these charts and the work should be accomplished in a short amount of time, even for a PBI novice like me.

Each chart is a simple dual-axis time series chart featuring different types of data. What I am interested in determining for PBI is the following:

1. Does Power BI have the fine-scale controls needed to reproduce the colors and/or the other chart elements like grid lines;
2. How long does it take to create these figures?
3. Are there any limitations in Power BI for these types of charts?
4. Can Power BI reproduce some of the other charts I created in Tableau in about 1 hour, as shown in the next section (the video). In particular, how does Power BI do when the time aggregation is set to month, or quarter, which I did for some of the extra graphics I produced?

Describing The Dam Power Generation Concept

In the following video, I describe the conceptual framework of this example. Essentially this example tests the ability of Power BI and Tableau to do a fairly fundamental operation, which is to plot time series data of theoretical power generation from a dam. The data resides in an Excel worksheet, so the volume of data is reasonable and the charts I want to reproduce are very typical for plotting time series data.

During the video you will see the Tableau reproduction graphics but you will not see the Power BI results because I have yet to create them at the time of this writing.

Dudley’s Original Excel Charts

Figures 4 and 5 (previously shown) are Dudley’s original charts that were produced by Excel within the Tellico.xls spreadsheet. These are the charts that this third test is trying to reproduce.

The time step used in the model is one day, spanning out over 37 years, for a total number of data points over 13,430. You can click here to download the Tellico Dam power generation spreadsheet.

The Tableau Results

The Tableau results were very quick to produce (about 30 minutes for 2 reproduced graphics, about 1 hour in total for all graphic shown below). There were no constraints observed and I even had a bit of extra time to do some additional exploring of the data, as exhibited by the collage of extra charts included below (Figure set 6).

Average annual precipitation by decade

Monthly revenue created over time

Quarterly moving average of reservoir storage vs precip

Daily power and cumulative revenue generation over time.

Monthly revenue >60K for the decade of the 2000’s

Monthly revenue >60K for the decade of the 2010’s

Monthly revenue >60K for the decade of the 1980’s

Monthly revenue >60K for the decade of the 1990’s

Daily power generation and 109 day moving average

The Power BI Results

I’ll start by saying that I don’t even mind having to update Power BI about every time I use it. This means that Microsoft is really working on product enhancements and it is always interesting to see what is new. I have gotten used to seeing the following update installation screen (Figure 7). The installation is quick and easy once the 115 MB file is downloaded.

Figure 7 – The Power BI update installation screen.

What I didn’t anticipate happening, however, is what came next (Figure 8):

Figure 8 – The *.xls files are not natively supported unless a certain driver is installed. Rather than installing the driver, I just saved the file in a *.xlsx format, which solved the problem as described below. I am wondering if Microsoft will add the installation of this driver to future versions of PBI so that users do not encounter this problem.

PBI Problem Solving

Before I could start the clock on determining how long it took me to reproduce Dudley’s graphics, I had to do a little problem-solving, PBI style. I was a bit confused because I previously had PBI 64 installed and didn’t have any trouble connecting to an Excel file.

As shown in Figure 9, the problem arose because Dudley’s file was an older style *.xls file. So instead of updating drivers, I simply re-saved the file in an *.xlsx format which solved my problem. I lost Dudley’s macros (I could have saved them by re-saving to a *.xlsm format) but it didn’t matter to me since I had already pasted the data values and wasn’t using his macros for computations.

Figure 9 – PBI cannot read *.xls files without certain drivers being installed. You can take the long road by installing the drivers, or take the  much easier 3danim8 road and save the file into a more updated version of Excel, which also solves the problem with less system overhead.

Creating the Graphics in PBI

The next challenge was to determine how to create a dual-axis chart like the ones I showed previously. Upon searching, I found this article which demonstrates a PBI dual-axis technique and indicates that:

Power BI supports a couple of different dual-axis charts. In this example, we will use a line and stacked column chart.

Well, this is not the type of chart I wanted to produce. I wanted to produce a dual axis chart with a time series of shapes on the left axis and a line chart on the right axis. Since PBI only gives me two dual-axis choices, I had to modify the remainder of this article to complete the product comparison.

Here are the two dual-axis chart types currently available in PBI:

1. line and stacked column chart
2. line and clustered column chart.

Therefore, I decided to create a type 1 chart at different levels of time aggregation.

Figure 10 is a line and stacked column chart featuring annual power generation (bars) and cumulative revenue generation (line). Technically this chart is accurate and reflects the data correctly. It was also easy to make.

However, without color controls for things like y-axis titles, it is bit difficult for the readers to immediately know which axes represent the bars and which represents the line. I suppose that readers might know the Microsoft standard for this, but it is potentially ambiguous.

Figure 10 – A line and stacked column chart, year aggregation. The bars are measured by the left side axis, the line by the right side axis.

Notes regarding this chart

It is possible that I simply could not find how to implement the following features when I created this chart. If this is the case, the software design is flawed because features are too hard to find. If these features simply do not exist, Microsoft needs to add them.

1. Axis titles could not be modified
2. Thickness of blue line could not be changed
3. No controls on text sizing, colors, or font for axes could be found
4. Exporting the image to jpg or other format is not available. As of two weeks ago (Oct 17, 2016), there is a new feature for exporting each report image to Powerpoint, but I did not have this capability showing up in my PBI desktop. All PBI charts shown in this article are screen-captured images because that is the only way I could determine how to save the charts.
5. When performing time drill-ins, the choices offered by PBI for dual-axis chart type are Discrete quarters, months, and days of the month. There are no continuous dates drill-ins natively available for this type of chart. I think extra work would need to be done to make this functionality accessible but I really don’t know how to do it yet. In this example, the drill-ins produced for the three additional time aggregations are not correct because of the nature of the cumulative time series data used for the secondary axis (cumulative $ revenue field). The revenue generation is a cumulative function over time, and when discrete dates are used to display the data (other than year), the line charts lose meaning and are inaccurate as shown in Figures 11 (a-c), although the data presented in Figure 10 is accurate. The cumulative revenue generated data field only works properly with the annual perspective previously shown in Figure 10.

Figure 11a – Quarterly power generation and total revenue generated (inaccurate)

Figure 11b – Monthly power generation and total revenue generated (inaccurate)

Figure 11c – Day of the month power generation and total revenue generated (inaccurate)

To gain insights into the three additional time aggregations, the revenue field had to be changed from a cumulative revenue perspective to daily revenue data, which fixes the three figures shown in Figures 11 (a-c) but changes the nature of the insights from Figure 10. All four time perspectives are shown in Figure 12 (a-d) using daily revenue generation data. Although I could write a quick calculation for Figure 12a to show the cumulative total over the years, I didn’t do that in this example to illustrate differences in software capabilities. Currently the only quick calculation available in the out-of-box PBI is the percent of grand total calculation. I used it in the example to see how it works, and the functionality is just as it would be in Tableau. PBI desparately needs additional quick calculations such as a running total function.

Figure 12a – Annual power generation and total revenue generated.

Figure 12b – Quarterly power generation and total revenue generated.

Figure 12c – Montly power generation and total revenue generated.

Figure 12d – Day of the month power generation and daily revenue generated.

Summary of PBI Graphing Capabilities

The total amount of time it took me to reproduce the PBI graphics was about 1 to 1.5 hours. Some of this time was needed to understand the graph types available in PBI and to do internet searches. To make sure that I did sufficient research on this topic, I did multiple searches on this topic, one of which is shown in Figure 13.

Figure 13 – Information available on dual-axis charts for PBI, 11/4/2016.

It is possible that someone has created a custom chart type for PBI that would be useful for me in this example. If it is available, I’d like to know about it.

The answer to my initial questions are now determined:

1. Does Power BI have the fine-scale controls needed to reproduce the colors and/or the other chart elements like grid lines;  (No)
2. How long does it take to create these figures? (1 – 1.5 hours, much quicker now after learning more about PBI)
3. Are there any limitations in Power BI for these types of charts? (Yes, many limitations)
4. Can Power BI reproduce some of the other charts I created in Tableau in about 1 hour, as shown in the next section (the video). In particular, how does Power BI do when the time aggregation is set to month, or quarter, which I did for some of the extra graphics I produced? (The time aggregations have to be carefully used. I didn’t bother trying to reproduce the additional charts due to time constraints.)

PBI was not able to reproduce the dual-axis charts that I thought would be easy to do. Dual-axis charting in PBI is limited to two conditions. Time drilling uses a discrete date approach, rather than a continuous time approach for the dual axis chart type I was using. There is a lack of fine-scale controls. In general, there were many limitation in PBI that prohibited me from reproducing Dudley’s original Excel charts.

Final Thoughts

I have learned a lot about PBI by doing these experiments. What I didn’t realize and fully appreciate until now can be summarized in a couple of paragraphs when comparing Tableau to Power BI:

Tableau is a very flexible system that allows data artists to paint masterpieces. They can combine graphical elements and place them on a virtually unlimited canvas. In a painting analogy, the artist can mix colors and can control at the finest level of detail every aspect of the painting that they are wanting to produce. There are very few limitations in Tableau, which gives the artist the ability to maximize their creativity.

In Power BI, the artist has the ability to create a lot of different types of standard and custom graph types. The custom graph types can be imported if they do not already exist in the product. Within each of these graph types, however, the ability to customize and fine-tune the product is very restricted. I suspect that much more functionality will be brought into the product over time, but right now there are a lot of deficiencies that need to be addressed which limit our ability to control what is produced.

Finally, this test reminded me of a story in a song called “Mr. Tanner” by the late-great Harry Chapin. Take a few minutes to listen to the story and see if you can see the connection to Power BI. The connection is very clear to me and it occurs at about 3:25 in the video.

He did not know how well he sang, he only heard the flaws.
But the critics were concise, it only took four lines.
But no one could accuse them of being over kind.

Mr. Martin Tanner, Baritone, of Dayton, Ohio made his
Town Hall debut last night. He came well prepared, but unfortunately
His presentation was not up to contemporary professional standards.
His voice lacks the range of tonal color necessary to make it
Consistently interesting.

Full time consideration of another endeavor might be in order.

Read more: Harry Chapin – Mr. Tanner Lyrics | MetroLyrics