Monday, October 7, 2013

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


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


  1. Hi Brad,

    No mention of Tableau's Quick Table Calculations here? In both cases, the quick table calculations are three clicks away (click on the pill to open the context menu, choose Quick Table Calculation, choose the relevant table calc), no coding is necessary. Personally, I think this is hands-down a win for Tableau, because Tableau users don't have to know about the ALL(), TOTAL(), or LOOKUP() functions or write any code to get these powerful functions.


    1. Jonathan,

      I completely agree with on that point. Tableau and Power Pivot both have a feature like that and that's exactly what the next post is going to be about! Thank you for your comments. They always remind me of another cool feature that I may not give enough credit to.

  2. I have one question. Does tableau can create a chart in tabular format without any measures, i mean like excel format. only dimensions. i tried a lot. but it says Abc in one column fully. How to hide that column alone ?

    1. Harihara,

      The easiest way is to create a calculated field equal to " ". For more info, check out the following link

  3. What about pretty standard dashboard layout like this:
    Apr-12 May-12 ∆(%)
    Discount 5.00% 5.20% 4.0%
    Product Base Margin 90 109 21.1%
    Profit $31,361 $29,371 -6.3%
    Profit Ratio 11.70% 7.64% -34.7%
    Sales $268,025 $384,588 43.5%
    Shipping Cost $2,084 $2,579 23.8%
    Time to Ship 9 9 0.0%
    In my real dashboard I have more columns (e.g. % of current month to 3 month average, same month year ago value and delta to the current month etc).
    As far as I know it's not possible to do it in Tableau. What about PowerPivot?
    With ambitions to be a dashboarding tool Tableau needs better version of crosstab view. When we moved to Tableau I had an assignment to recreate in Tableau one of the Excel dashboards that we delivered to our client monthly. Part of that dashboard was similar to my example above. I coudn't make it in Tableau. I suggested different layout, but business didn't want to change: client liked it.

    1. From the layout you gave, I don't see any reason why Tableau wouldn't be able to do it. However, I will say that you will have to get clever with your design. I can say the same for Power Pivot. Would it be possible for you to send me an email? I could show you how to recreate that chart in either (or both) of the tools if you would like.

  4. Hi Brad,
    Am just starting out in data analytics. Your explanation and comparison helps a great deal. Keep them coming.
    Good Luck!