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) |
| Profit Ratio by Region (Power Pivot) |
| Profit Ratio (Tableau) |
| Profit Ratio by Region (Tableau) |
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) |
| Percent of Total Sales (Tableau) |
| 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.
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) |
| Previous Year's Sales |
| Previous Year's Sales by Year (Tableau) |
Winner: Tie
Summary
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.
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
http://breaking-bi.blogspot.com
.png)
.png)
.png)
.png)

.png)
.png)
.png)