Tableau vs. Power Pivot Part 5: Point-and-Click KPIs

Today, we will talk about what we are calling "Point-and-Click KPIs."  These are calculations that can be performed using built-in non-coding functionality within these two tools.  To be a little more clear, Power Pivot has a feature called "KPI" which allows the user to quickly compare measures to goals with nice visual output.  On the other hand, Tableau has a feature called "Quick Table Calculation" which allows the user to quickly create some advanced calculations with only a few clicks.

In our previous post, we compared the tools on how you could programmatically create some simple KPIs.  In other words, we compared "apples to apples."  In this post, we will be comparing related features of these tools that are not quite so easily compared.  Therefore, there will be no winner assigned in this post; it is purely for informational purposes.  On another note, the term KPI takes on somewhat of an ambiguous meaning in this post.  We typically refer to run-time calculations as KPIs.  It turns out that Power Pivot's feature is also called KPI; however, we'll try to be deliberate about our word choices.  Now, let's get on with the examination.

Power Pivot KPIs

In the Power Pivot ribbon (among other places), you will see an icon that says "KPI."  Clicking on this icon will allow to select "New KPI" or "Manage KPIs."  Click on a KPI, or creating a new one, will open up the following window:

KPI Window (Power Pivot)

As you can see, this window allows you to define your base value, a goal (calculated or absolute) and a display mechanic to be associated with that KPI.  The KPI will calculate a simple ratio to determine how close the base value is to the goal.  You can adjust the acceptable percentages using the sliders on the multi-colored bar in the middle of the window.  You can also change how the bar is arranged to allow for a little bit more flexibility in your KPI.  Now, let's create a KPI that compares this year's sales to last year's sales.

Sum Sales KPI (Power Pivot)

Sum Sales KPI by Region and Year (Power Pivot)

Now, we can see which Years saw a decline in Sales for each Region.  There are a couple of things to note about this feature.  First, you can only have one KPI per measure.  If you want to compare this year's sales to last year's sales and this year's sales to this year's budget, you will need to duplicate your measure for SUM( [Sales] ).  Second, you need to create each underlying measure individually and you cannot use implicit measures.  Third, if you select any display styles other than the default Red-Yellow-Green Circles, Excel will still display them as Red-Yellow-Green Circles.  You can change the display style by editing the conditional formatting.

To be honest, this feature is not very useful within Excel.  The KPI calculation can easily be made using [Base] / [Goal].  Also, the target audience for Power Pivot is Excel Power Users.  These Power Users should already have a good understanding of how to create conditional formatting.  Therefore, we don't see many people using this feature inside of Excel.  However, this feature does become useful when you are creating dashboards inside of Power View, which does not have built-in functionality for conditional formatting.  When you are using these KPIs inside of Power View, you will see that changing the display style of the KPI will be reflected in your dashboard.

So, we will end this examination by saying that this feature is neat, albeit weak, addition to the Power View Dashboard toolkit.  It allows the users to spice up their text tables to add that next level of usability, and we all know that text tables are an Excel Users best friend!

Tableau Quick Table Calculations

Whenever you drag a measure onto a chart in Tableau, you can right-click the measure and select "Quick Table Calculation."  This will open up a list of common calculations that Tableau will calculate for you.

Quick Tableau Calcuation (Tableau)

If you select a calculation, Tableau will replace the measure on your chart with the calculation you selected.  Now, let's calculate Year over Year Growth using this feature.

 

Year over Year Growth by Region (Tableau)

This took one click and we have a calculation that most people would have to think about how to calculate.  This calculation would also take some knowledge of table calculations to create on your own.  What if you wanted to know how Tableau calculated this value?  Right-click the SUM( [Sales] ) pill on the Text Shelf and select "Edit Table Calculation", then select "Customize."  This will open up the Calculated Field window where you can see the exact syntax for creating this calculation.  In fact, this is how we learned table calculations when we were first getting started.  It should also be noted that this feature can be used on implicit measures, like SUM( [Sales] ), or any calculated fields you want to create, except for other table calculations.  All in all, this is an extremely easy way to create most of the common calculations you would want, and it's not a bad way to improve your Tableau skills either.

Conclusion

Power Pivot's KPI feature is an easy way to add a little color and readability to your Power View dashboards.  It also allows you to easily create and maintain simple indicators in one central location.  On the other side, Tableau's Quick Table Calculation feature allows the user to create some complex calculations such as Year over Year Growth and Moving Averages with only a couple of clicks.  It is also useful for learning how to better utilize table calculations in Tableau.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

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