Add Total Values for Stacked Column and Stacked Bar Charts in Excel

This is the only video you need to learn how to add add totals to stacked bar charts and stacked column charts in Excel. Make sure to watch the video because I have you covered both ways.

I’m more of a video explanation person, so make sure to watch the video so the steps are clearer. Then refer to the bulleted steps as reference once you have the initial basic understanding.

The Keys to Adding Totals to the Stacked Column Chart Above:

  • Add a “Grand Total” column to your data
  • Highlight your data not including the “Grand Total” column
  • On the “Insert” menu select a “2-D Stacked Column” chart
  • Select “Switch Row/Column” as necessary so your first data column is listed on the X axis
  • Select the chart and then expand the data range to include the “Grand Total” column
    • Add “Data Labels” to the Grand Total series on the chart
  • Right click on the Grand Total and “Change Series Chart Type”, the viz becomes a “Combo” chart
  • Change the “Grand Total” series chart type to a “Line”, while leaving all others as a “Stacked Column”
  • Format the line’s data label, changing the Label Position to “Above”
  • Select the line, format the data series and change the “Line” option to “No Line” in order to leave only the totals and hide the line.

The Keys to Adding Totals to the Stacked Bar Chart Above:

  • Add both “Grand Total” and “Spacing” columns to your data
    • Highlight your data including the “Spacing” column but not including the “Grand Total” column
      • The “Spacing” column should have a value of 0 at this point
    • On the “Insert” menu select a “2-D Stacked Bar Chart”
    • Select “Switch Row/Column” as necessary so the “Spacing” values are not listed as an option on the Y axis
    • Change the “Spacing” column values to a number (e.g., 1000) big enough to make a new category visible on the stacked bar chart
    • Right click to “Format Data Labels” and change the “Label Options” to “Value from Cells”
      • In the “Select Data Label Range” pop up box, highlight the values from the “Grand Total” column
      • Change the “Label Position” to “Inside Base”
    • On the chart select the Grand Total Series and right click so the “Fill” option appears
      • Change the “Fill” to “No Fill”
    • Change the values in the “Spacing” column to 0
    • Delete the “Grand Total” entry from the Legend

You can thank me by liking and subscribing to my YouTube Channel

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

Do Great Things with Your Data

– Anthony B. Smoak

How to Filter your Tableau Viz in Tooltip (Top 10 Values)

Visualizations in tooltips, affectionately know as “Viz in tooltip” is a handy feature available in Tableau that enables “details on demand” functionality. As the user hovers over a specific mark or data point, additional details are revealed that are filtered specifically for that mark from another worksheet.

In the example above, as the user hovers over a bar, they obtain additional details about the three most profitable products associated with the respective bar.

As I learned in a very informative Tableau presentation for tooltip wonks (myself included), the underlying architecture is built upon action commands and shares many commonalities with action filters. For viz in tooltip performance considerations, use smaller and fewer visualizations. Also try to avoid maps and other complex visualizations that have significant mark density.

If your tooltip responsiveness is greater than 2 seconds or the height and or width is greater than 600 pixels, then consider rethinking your approach. According to Tableau best practice, users are not willing to wait more than 2 seconds hovering over a mark for a reveal.

Since the viz in tool tip passes filters between worksheets, this means we can make use of context filters (click this link for a fantastic overview) to limit the number of marks returned and help improve performance.

This is the Section You are Here for

Context filters also help solve the problem of returning the Top N records associated with a mark. When you assign a viz in tooltip on your source sheet, a set filter is applied on the target (i.e., viz in tooltip) worksheet. If you’re a frequent watcher of my videos you know that the Tableau order of operations prevents the default set filter from returning a proper Top N.

By adding the set filter to the context on the Order of Operations skyscraper, the data is pre-filtered by your dimension first (e.g., State) and then the Top N filter is applied. When the set filter turns gray, you know it’s working.

Notice that the Context Filter box is above the Sets entry; which means that the Context filter is evaluated BEFORE the set. Make sure to watch the video to learn how to limit to the Top 10 cities based upon a hovered state.

Check out the video for details and may all your viz in tooltips be context appropriate!

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

Do Great Things With Your Data

-Anthony B. Smoak

Create Tableau KPIs Quick and Easy (Profit vs Budget)

In this video you will learn a quick and easy approach to putting together KPIs that illustrate how profits perform in relation to a budget (i.e., targets vs actuals). Of course the KPIs will be interactive thanks to help from parameters.

Now the dashboard that I built around the KPIs will definitely form the basis of additional videos. There are several techniques here (filtering viz in tooltips, show/hide container, bar in bar chart, reset all filters button, etc.) that form the basis of a good intermediate level dashboard.

Inspiration comes in many forms. I have to give a shoutout to Keith Dykstra for offering his original dashboard for reverse engineering. The idea for the KPIs and bar in bar chart are inspired by Keith. I added additional elements such as the reset all filters button, filtering by states via treemap, parameters in lieu of hover over images.

Shoutout to the Kevin Flerlage for his great PowerPoint button workbook. I modified the on/off toggle button based upon a template from Kevin’s workbook.

Finally, I was watching an Oregon vs UCLA football game one Saturday and was impressed by Oregon’s latest uniform combination. I had to throw that grey and green combination together for use on the dashboard. Inspiration can come from many places! Here’s a little Oregon football and my “Saturday Night Lab” tweet.

Make sure you watch this video to learn and hopefully get inspired yourself!

If you’re interested in KPIs you can check out these other videos:

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

Tableau Bar Chart: Combining Small Values

In this video we’ll learn how to build a bar chart visualization that combines values below a certain threshold into an “OTHER” bucket. This technique is very useful when limiting the number of bars to show on your visualization while not losing sight of all the smaller values.

  • Using standard Tableau Superstore data, we’ll calculate the percentage of sales that are generated by each individual state.
  • We’ll then use a parameter to set a percentage threshold where all states below this percentage will be combined.
  • This technique also requires the use of sets and Fixed LODs.

This technique allows us to combine all states below a certain threshold (e.g., 2%) into one single bar chart showing a combined 24%.

Bar Chart Below Threshold Thumb 01

I have to give credit where credit is due to Ann Jackson for sharing this technique at TC19!

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

 

Build a Stacked Donut Chart in Tableau

Have you ever wanted to stack 4 pie charts on top of each other to build a visual? Let’s have some fun building out a stacked donut chart or a “TrailBlazer” chart as I call it due to its likeness to a particular NBA team’s logo.

Stacked Donut Chart Thumb

Portland Trailblazers anyone?

Stacked Donut vs Trailblazer

In order to build out this chart I used an innovative technique shared by Simon Runc on the Tableau forums. Feel free to check out that post here.

Using Tableau’s Sample Superstore Data, Simon came up with an innovative use of the INDEX() function and the Size functionality to create three different pie charts that each show a respective percentage of a measure (in this case Sales) to the Total amount of the measure.

For example, the chart highlights in red the percentage of Consumer Sales as a percentage of all segment sales (i.e., Consumer + Corporate + Home Office). The grey portions represent all other sales, other than the segment of interest.

The trick to this approach is using the Index function to create a pie chart per segment. For example Consumer is assigned a value of 1, Corporate a value of 2 and Home Office is assigned 3. When the INDEX() value is placed on size, the three different charts are assigned sizes where one is slightly larger than the next.

Stacked Donut Raw 2

With a little division and axis customization, the three segments are placed on top of each other to provide a stacked pie chart effect. The hole is courtesy of the standard methodology for creating a donut chart which involves a dual axis.

It makes much more sense when you see it in action so make sure to watch the video!

Here is an example of the raw stacked donut chart before the “TrailBlazer” formatting.

Stacked Donut Raw

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Definitely check out other posts of interest for building donut charts in Tableau:

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

Create a Tableau Waffle Chart Fast and Easy

In this Tableau tutorial I am serving up some delectable waffles in the form of a fast and easy waffle chart. Watch the video to learn the easiest and quickest way to create a waffle chart in Tableau.

If you’re familiar with the Southeast United States then you know that we love The Waffle House down here. As an homage, I made a simple dashboard in the iconic Waffle House signage style.

A waffle chart depends upon a data connection to the data you wish to visualize and a data connection to the waffle chart template. Once you have these two items setup, you simply create a calculated field that marks the fill percentage in your waffle.

Help yourself to some waffles below:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

The Ultimate Tableau Slope Graph Video

In this video I tackle the subject of slope graphs also known as slope charts. I had some fun putting together this dashboard that illustrates the changes in wins for NBA teams during the 2016-2017 and 2017-2018 seasons. From the video you’ll discover that Chicago, Atlanta and Memphis are on a Hindenburg-like trajectory, while trusting the process in Philadelphia led to huge season gains in overall wins.

Here’s what you will learn from this video:

  • How to create a parameter that enables a user to select which win statistic measure to visualize;
  • How to use Table calculations like, LOOKUP(), FIRST() and LAST() to calculate period over period change;
  • How the impact of Mike Conley’s injury affected the Memphis Grizzlies last season;

Click the pic to interact with the Tableau Public visualization, also download the workbook and data to dissect as needed.

For your convenience the calculated fields that I used to create the measures are listed here. Note that [Selected Measure] is a parameter that you need to create that lists all of the measures.

Calc Select Measure
CASE [Selected Measure]
WHEN “Home Losses” Then [Home Losses]
WHEN “Home Wins” Then [Home Wins]
WHEN “Overall Losses” Then [Overall Losses]
WHEN “Overall Wins” Then [Overall Wins]
WHEN “Road Losses” Then [Road Losses]
WHEN “Road Wins” Then [Road Wins]
WHEN “vs East Conf Losses” Then [vs East Conf Losses]
WHEN “vs East Conf Wins” Then [vs East Conf Wins]
WHEN “vs West Conf Losses” Then [vs West Conf Losses]
WHEN “vs West Conf Wins” Then [vs West Conf Wins]

END
Better or Worse
IF [Selected Measure] = “Home Wins” OR
[Selected Measure] = “Overall Wins” OR
[Selected Measure] = “Road Wins” OR
[Selected Measure] = “vs East Conf Wins” OR
[Selected Measure] = “vs West Conf Wins”
THEN
//WIN MEASURES: Negative delta treated as “WORSE”, Positive delta treated as “BETTER”
(IF [Delta] < 0 THEN “WORSE” ELSEIF [Delta] = 0 THEN “SAME” ELSE “BETTER” END)
ELSE
//LOSS MEASURES: Positive delta treated as “WORSE” (more losses are worse), Negative delta treated as “BETTER”
(IF [Delta] > 0 THEN “WORSE” ELSEIF [Delta] = 0 THEN “SAME” ELSE “BETTER” END)
END
Delta
LOOKUP(SUM([Calc Select Measure]),LAST()) – LOOKUP(SUM([Calc Select Measure]),FIRST())
Delta ABS Value
ABS(LOOKUP(SUM([Calc Select Measure]),LAST()) – LOOKUP(SUM([Calc Select Measure]),FIRST()))
ToolTip
<Team> Trend: <AGG(Better or Worse)> by <AGG(Delta ABS Value)>
During the <Season> Season, the <Team> had <SUM(Calc Select Measure)> <Parameters.Selected Measure>.

I have to give thanks to Ben Jones at the Data Remixed blog for the inspiration!

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Filter Top N Values with a Slicer in Power BI

In this video you will learn how to filter the top N values shown in your bar chart visualization using a slicer.

  1. This technique uses one measure that generates a number 1-10, that will be applied to a slicer.
  2. Another measure will basically rank all of the values associated with your data bars and only return the values that are less than or equal to the number you select in the slicer.

The comments that I apply to the DAX function should help make it easy to understand. I have to give a shoutout to GilbertQ from the PowerBI community for coming up with the  initial approach which I tweaked for the video.

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel!

Row and Column Highlighting in Tableau

In this post you’ll learn how to highlight values in your Tableau table using set actions. The dashboard in this video displays the number of total points scored by NBA teams by position in the 2017-2018 season. I will give you step by step instructions on how to implement row and column highlighting on this dataset downloaded from basketballreference.com.

I’ve only made a few minor tweaks but this technique was developed by Tableau Zen Master Matt Chambers. You can check out his blog at sirvizalot.com and follow him at Big shout out to Matt for sharing this technique with the Tableau community!

You can interact with my visualization on Tableau Public:

If you find this type of instruction valuable make sure to subscribe to my Youtube channel!

Make Visually Appealing Maps in Tableau with Mapbox

The default maps in Tableau are just fine but sometimes you need to kick up the flamboyancy factor in your visuals. Integrating maps from Mapbox with Tableau is the perfect way to add some Liberace flash to your development game.

Mapbox is an open source mapping platform for custom designed maps.  By creating an account with Mapbox, you can either design your own maps on the platform or use their preset maps, which are all more impressive than the out of the box option in Tableau.

All you need to do is enter your generated API token (provided by Mapbox) into Tableau’s Map Services interface and you’ll have access to some pretty impressive mapping options.

If you’re interested in Business Intelligence & Tableau subscribe to my  Youtube channel.