Tableau vs. Power Pivot Part 7: Basic Dashboards

Today, we will talk about creating dashboards in Tableau vs. creating dashboards in Power Pivot and Power View.  To keep things basic, we will create dashboards that consist of a few related charts with some basic linking (filtering) and brushing (highlighting) features.  For those of you who are avid readers of this blog, you will note that we do not recommend these types of dashboards.  They are much less useful than they could be.  However, they are what the vast majority of business users tend to create.  As usual, we will use the Superstore Sales sample data set from Tableau.

This test will have three criteria: Ease of Use, Functionality and Aesthetics.  However, before we can begin the test, we need to design our dashboard.  Let's start with a map of Profit by State.  This map will lead you to a list of customers within that state, as well as how much profit they have generated and how many orders they have placed.  Finally, this chart will lead you to a table with information about all of the orders that customer has placed.  Now, let's see how Power Pivot handles this.

Phase 1: Power Pivot

First, let's try to create our map.

Profit by State (Power Pivot)

This looks decent.  However, a little color would be great to distinguish the profitable states from the unprofitable ones.  In order to do this, we need to create a calculated column with some type of discretization of Profit by State.  First, we need to create a calculated column with the Total Profit for each State.

Total Profit by State (Power Pivot)

We chose to use the ALLEXCEPT() function to do this.  There are likely other ways to do this using SUMMARIZE() or some other filtering function.  We also checked this by comparing the value for the states to the values in map.  Now that we know how much profit the state has generated, we can categorize them.  Looking at the map, we see values between -$10,000 and $100,000.  So, let's categorize them in groups of $25,000.

Profit Category by State (Power Pivot)

This wasn't too difficult of a task.  However, these boundaries are hardcoded and would have to be manually changed if the data changed.  Let's see how it looks on the map.

Profit by State (Improved) (Power Pivot)

This map is far more useful than the previous one.  You can easily tell which states belong to which categories.  However, we would love to be able to change the colors so that they follow a recognizable pattern, such as Red-Green, or Blue-Orange.  Alas, this is not possible in Power View.  We could create a much better map in Power Map.  However, we are saving Power Map for a more advanced investigation in a later part.  

Let's move on to the bar graph of Profit and Number of Orders by Customer.  In order to do this, we need to create a Number of Orders measures.

Number of Orders (Power Pivot)

This was easy enough.  Now, let's create the bar graph.

Profit and Number of Orders by Customer (Power Pivot)

This chart isn't bad.  However, we are unable to show Number of Orders in any reasonable way because of the massive difference in scale between the two measures.  We could put it on its own separate graph; but then we would lose the ability to sort by Profit.  So, it seems that must scrap Number of Orders from this chart.  We also have this ugly ribbon telling us that there are too many customers to display.  We could go back and create another calculated column which tells us which customers are in the Top N%.  Although, that's a lot of work.  It's easier to just filter out customers whose profit is less than $500 and move on.  Removing possibly valuable information is not a great thing.  Alas, the tool leaves us little choice.

Finally, let's create a text table that we can filter down to each customer to show what they have purchased.

Order Details (Power Pivot)

There's nothing too complex about this table.  It would be nice to have a very light amount of color to differentiate profitable orders from unprofitable ones.  Once again, Power View is not nice enough to give us such a feature.  All in all, this is a nice dashboard.  It tells us the information we need to know, when we need to know it.  

As far the dashboard construction goes, Power View automatically creates links between charts that allow for an interactive experience.  Therefore, all we need to do is place the charts on the sheet.  We'll show the final reveal at the end.  Now, let's see what Tableau can do.

Phase 2: Tableau

Once again, we will start with the map.

Profit by State (Tableau)

Not only did this only take a few drags, it looks significantly cleaner than Power Pivot.  Next, let's try the bar graph.  First, we need to calculate Number of Orders.  As we saw in a previous post, this is not a simple task in Tableau.  We need to start with Order ID in the chart.

Profit by Customer and Order ID (Tableau)

Once again, this was just a few drags.  The real key is knowing when you are going to need to use these advanced techniques.  Now, we can calculate Number of Orders.

Number of Orders (Tableau)

Number of Orders (Compute Using) (Tableau)

We see that Number of Orders is already at the Customer level.  Next, we need to aggregate Profit up to the Customer level.

Total Profit by Customer (Tableau)

Total Profit by Customer (Compute Using) (Tableau)

This was very similar to the Number of Orders, albeit using a different table calculation.  I could have also achieved this with the TOTAL() function.  Now, we need to aggregate out the Order ID to get the chart to look the way we want it to.

Aggregator (Tableau)

Aggregator (Compute Using) (Tableau)

Finally, we need to clean up the chart by removing the extra horizontal lines, hiding the Order ID header and adding labels to the bars.

Add caption

Creating this chart was significantly more involved than creating the one in Power View.  However, this chart is far superior in both usability and aesthetics.

EDIT: This could have been done significantly more simply by using the COUNTD() function, which doesn't exist in live Excel connections.  The solution would be to create an extract and use the COUNTD() function from that source.

Now, on to the Order Details table.

Order Details (Tableau)

As you can see, Tableau allowed us a tremendous amount of flexibility when creating this table.  This version is far more useful to the analyst than just a table full of numbers would be (which is exactly what we used in Power View).  Unlike Power View, Tableau does not automatically link sheets on a dashboard.  Each "Action", as they are called in Tableau, must be created individually.  While this is more work, it leads to a much more flexible product.  First, we need to be able to filter the bar graph and table using the map.

State Filter (Tableau)

This box shows us just how flexible Tableau's Action system is.  Take note of the "Exclude all values" selection.  This will allow us to demonstrate a neat trick that, to our knowledge, Power View cannot do.  Next, we need to allow the table to be filtered by the bar graph.

Customer Filter (Tableau)

This filter is very similar to the State Filter, albeit with a few different selections.  Finally, let's move on the main event.

Phase 3: Dashboard Showdown

First, let's see how Power View turned out.

Power View Dashboard (No Selections)

This dashboard looks very clean.  The white background and smooth colors give it a very professional feel.  We can easily see which states belong to which categories.  However, the color scheme is a little confusing.  Shouldn't Green be a good thing?  The bar chart of customers is very clean as well.  We can easily see that there are three customers who generate more profit than the rest.  The table at the bottom is pretty useless as of now; there's way too much information in it and it's sorted by date.  Now, let's select a state and see what happens.

Power View Dashboard (State Selected)

The bar graph is now highlighted, showing us how much of the customers' profit is coming from this state.  The table also filtered to only show orders from this state.  It does seems somewhat difficult to find which customers actually purchased in this state, there are too many empty bars in the bar graph.  Also, the table is still far too large to be useful for some type of analysis.  Next, let's select a Customer and see what we get.

Power View Dashboard (Customer Selected)

We can somewhat see which states this customer ordered from.  However, the wild colors make this process slightly less desirable.  Finally, we see that we have a useful table at the bottom.  We can see all of the orders placed by this customer.  However, it would be nice to be able to easily see which of these orders  were profitable and which are not.  All in all, this was a nice dashboard.  It was very clean and professional.  An analyst would be able to make good use of this dashboard.

Lastly, let's see how Tableau fares.

Tableau Dashboard (No Selection)

This is really crisp.  The colors are not overpowering and it is easy to see where each state ranks.  The map takes up the entire screen, which allows us to see what's going on in New England.  Let's select a state and see what happens.

Tableau Dashboard (State Selected)

Cool!  The map automatically resized to allow room for the bar chart.  Hint: This was caused by that "Exclude all values" selection we mentioned earlier.  We can also see how profitable each of the customers were in this state.  As a bonus, we can see how many orders they placed.  Apparently Rick Reed placed one really big order, as opposed to most of the other customers, who place series of smaller orders.  We would have never seen that using the Power View dashboard.  Let's select a customer and see what happens.

Tableau Dashboard (Customer Selected)

Everything resized again to allow room for the table.  The table also allows us to easily see which purchases were profitable and which weren't.  As an added bonus, Tableau allows us to fit more columns in our table.  Who knows when that extra information might come in handy?  There is one irritation we would like to point out.  When you select charts in Tableau Desktop, you get those borders around the chart.  It is very difficult to avoid this and we wanted to show it.  Power View also does this; but it is significantly less pronounced.  All in all, this dashboard is terrific.

Conclusion

It was pretty easy to see that Tableau blew Power View away when it came to dashboard functionality.  However, some of the techniques employed in Tableau were advanced and/or time-consuming.  So, we must give credit to Power View for its simplicity.  But, we believe that Tableau demonstrated so much more flexibility and analytical capabilities that it is worth the extra effort.  It is worth noting that Power View is a free tool that comes with Office 2013.  Therefore, if you are looking for a first-step into the world of visualization, at little upfront cost, then Power View could be a good start for you.

Winner: Tableau

If you are interested in learning how to create the dynamically sizing charts in Tableau, check out Creating a Dashboard with Variable Container Heights in Tableau.  This post was written using Tableau 7.0; but the task didn't change much in 8.0.  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