Tableau vs. Power Pivot Part 4: KPIs

Today, we will talk about creating KPIs.  More generally, we will talk about creating more complex functions than simple aggregations.  One of the defining characteristics of a KPI is that it is a measure that cannot be calculated at the row or column level; it must be calculated in real time.  By this definition, KPIs are also non-additive or, less commonly, semi-additive.  KPIs are the backbone of most enterprise reporting and should weigh very heavily on the choice of a tool.  Therefore, we will attempt to examine them very closely.  As usual, we will use the Superstore Sales sample data set from Tableau.

Simple Ratio (Profit Margin)

Unfiltered Ratios are some of the simplest KPIs.  So let's look at how to calculate Profit Ratio by Region in Power Pivot.

Profit Ratio (Power Pivot)

This calculation is quite simple.  It's also nice that Power Pivot lets you select the format here.

Profit Ratio by Region (Power Pivot)

As you can see, this was simply one calculation and two drags.  Let's see how Tableau fares.

Profit Ratio (Tableau)

This formula is just as simple as the one for Power Pivot.  Now, on to the chart.

Profit Ratio by Region (Tableau)

This chart looks identical to the Power Pivot chart.  However, we did have to go through one extra menu to set the format of the KPI.  Also, you might notice that Power Pivot is showing a total while Tableau is not.  For KPIs, totals may or may not be significant.  Since this is such a situational need, we do not consider it when deciding our verdict.  The extra menu was enough to give Power Pivot the edge in this category.

Winner: Power Pivot

Filtered Ratios

To the untrained user, a filtered ratio is the same as a simple ratio.  They simply see it as a non-additive measure.  However, they are much more complex to create because the creator must have an awareness of the current filters, as well as how to alter them.  Let's see how Percent of Total Sales per Category works in Power Pivot.

Percent of Total Sales (Power Pivot)

The calculation leverages this nifty ALL() function which does the heavy lifting for us.  Now, on to the pivot table.

Percent of Total Sales by Category (Power Pivot)

As usual, the pivot table was a breeze to create.  Let's give Tableau a shot.

Sales per Category (Tableau)

In typical Tableau fashion, we have to make our chart first.  Now, let's take a look at the calculation.

Percent of Total Sales (Tableau)

Just like with Power Pivot, we can leverage a nice function here.  In fact, the TOTAL() function in Tableau works pretty similarly to the ALL() function in Tableau.  Finally, on to the chart.

Percent of Total Sales by Category (Tableau)

As usual, creating the charts is a drag-and-drop experience.  These exercises seemed almost identical between the tools.  They both leveraged similar functions to accomplish an identical task.  Not much else to say about it.

Winner: Tie

Lookups (Previous Year's Sales)

Lookups are another important part of KPIs.  Many companies like to compare KPIs for this period to KPIs from a previous period.  Let's see how it's done in Power Pivot.

Previous Year's Sales (Power Pivot)

This DAX expression is more complex than the last one.  Power Pivot prefers your data to be in a Star Schema.  Therefore, many of the DAX functions, especially the time intelligence ones, will leverage this.  However, this also means that there is a learning curve associated with these functions.  Let's check out the pivot table.

Previous Year's Sales by Year (Power Pivot)

The pivot table was easy to create once the DAX was finished.  Let's check out Tableau.  Remember that Tableau table calculations work based on what's in the chart.  So, we have to make our chart first; then we can calculate our KPIs.

Sales by Year (Tableau)

Now, we can make our calculation.

Previous Year's Sales

Finally, we can finish our chart.

Previous Year's Sales by Year (Tableau)

This wasn't too complicated if you know how table calculations work.  The predicament is this: We took two very different paths to get these answers.  So, how do we compare?  Personally, we feel that Tableau's "Canvas" concept is a little bit easier to grasp because it focuses on what you can already see in front of you.  However, Power Pivot has this handy PREVIOUSYEAR() function which almost does the work for you.  Therefore, we can't make up our minds on this one.

Winner: Tie

Summary

All in all, there's not much of a difference between the difficulty of using these tools.  Tableau's "Canvas" concept is very intuitive; however, it always makes u go through extra steps to get the formatting right.  Power Pivot seems to finish much cleaner; however, it requires a pretty star schema behind it (which does take time to create).  Therefore, we'll call this whole part a tie.  If you are looking to create these simple KPIs, feel free to pick the tool that you like best.

On a related note, some of the great features of Power Pivot are its built-in “Time Intelligence” functions.  We used PREVIOUSYEAR() earlier, but that’s just the tip of the iceberg.  We will devote an entire post to these types of analyses.  Thanks for reading.  We hope you found this informative.

EDIT:  Zen Master Jonathan Drummey keenly stated that no credit was given to Tableau's QUick Table Calculation feature.  On the other side, no mention was made of Power Pivot's KPI feature.  Both of these are extremely valid points.  These features are very useful for business users and the next section will be entirely devoted to these features.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn
http://breaking-bi.blogspot.com