Questions from Tableau Training: When to Pivot Data in Tableau

Data

Questions from Tableau Training: When to Pivot Data in Tableau

by Jon Bajon

Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

A common question that often comes up in trainings is:

“How do I utilize the Pivot feature in Tableau to make building charts easier?”

What does it mean to pivot data? A pivot is a transformation of data structure from a wide view (many columns) to a long view (condensed columns). There are many benefits to pivoting data, one of which is the ease of building charts and sorting those charts dynamically. 

Let’s build a chart that shows our quarterly sales for some of our top selling sub-categories and show them in a descending order:

Sub-categories descending order

Combing Category, Sub-Category and Q1 Sales, the bar graph shown above was created. I also want to show Q2-Q4 Sales in a stacked bar chart. This can be accomplished by dragging Q2 Sales onto the Q1 Sales axis already in place until the double green rulers appear. This technique invokes Measure Names and Measure Values.

Ruler

This process can be repeated for Q3 Sales and Q4 Sales until the chart looks similar the one below:

New Tableau chart

I do not want to see a separated bar for each quarter. Moving Measure Names from Rows and dragging it to Color would create a stacked bar chart. After making a few color and axis formatting changes, we can get the chart below:

Stacked bar chart

Lastly, it is important to dynamically sort Sub-Categories by adding all the quarterly sales together in a calculated field:

Quarterly Sales Total

I’ll add the sort on the new Quarterly Sales Total field and get the final chart shown below:

Final stacked bar chart

The same chart can be built much quicker if the data is pivoted. Edit the original data source to create the pivot by CTRL + clicking the columns and selecting pivot from the drop-down list:

PIVOT!

A quick rename of the two pivoted fields, Quarters (which holds the names of Q1 Sales – Q4 Sales) and Quarterly Sales (which holds the sales values for each record of Quarters). In this example, the number of rows quadrupled because the previous four individual columns were consolidated and displayed as rows.

Starting off with Category and Sub-Category, we can create the basis of the chart, but this time using our new pivoted field Quarterly Sales in place of Q1 Sales:

basis of the chart with pivoted field

Dragging Quarters to color on the Marks card will break out the bars into the four different quarters, only leaving the dynamically descending sort left to add. Hovering over the Quarterly Sales axis will reveal the quick sort button, which will ensure that the sort is dynamic on the Quarterly Sales field.

FINAL RESULT with pivoted field!

Pivoting can be a useful method for building charts more efficiently and displaying visualizations with little effort.   

Want More Questions from Tableau Training

  1. Questions from Tableau Training: Quick Filter Interaction
  2. Questions from Tableau Training: Labeling with Table Calculations
  3. Questions from Tableau Training: Speeding Up Presentation Views when Using Tableau
  4. Questions from Tableau Training: Dashboard Actions
  5. Questions from Tableau Training: Business Day Calculations
  6. Questions from Tableau Training: Adding Gaps Between Bars
  7. Questions from Tableau Training: When to Pivot Data in Tableau
  8. Questions from Tableau Training: Adding Conditional Formatting
  9. Questions from Tableau Training: Setting Weekday Start
  10. Questions from Tableau Training: How Do ZIP Codes Work in Tableau?
  11. Questions from Tableau Training: The Small Grey Bar Within a Quick Filter
  12. Questions from Tableau Training: Matching Mark Color
  13. Questions from Tableau Training: Showing the Null Indicator
  14. Questions from Tableau Training: Can I Manually Set the Colors of a Stepped Gradient?
  15. Questions from Tableau Training: How Can I Get Box Plot Data into a Crosstab?
  16. Questions from Tableau Training: How Do I Clear a Worksheet? Make a Copy?
  17. Questions from Tableau Training: Opening URL Actions Inside of a Dashboard
  18. Questions from Tableau Training: Can I Move Mark Labels?
  19. Questions from Tableau Training: How Can I Use My Current Report to Create a Viz?
  20. Questions from Tableau Training: Can I Create Text Hyperlinks on a Dashboard?
  21. Questions from Tableau Training: How Can I Transform My Crosstab into a Flat List?
  22. Questions from Tableau Training: Labeling the Top N
  23. Questions from Tableau Training: How Can I Customize Charts with Parameters?
  24. Questions from Tableau Training: Combining Measures into a Single Line
  25. Questions from Tableau Training: How Can I Create Custom Color Palettes?
  26. Questions from Tableau Training: Can a Top N Parameter be Nested Within Other Fields?
  27. Questions from Tableau Training: Conditional Scope on Tableau Calculations
  28. Questions from Tableau Training: Top Ten Plus N
  29. Questions from Tableau Training: Dynamic Label Positioning
  30. Questions from Tableau Training: Using Cross Joins to Solve Active Tickets
  31. Questions from Tableau Training: Top N Highlight Table
  32. Questions from Tableau Training: How to Use Specific Dimensions in Table Calculations
  33. Questions from Tableau Training: Moving Reference Line Labels
  34. Questions from Training: Comparing Metrics for Same Weekday in Previous Year
  35. Questions from Tableau Training: Colouring Specific Columns and Rows in a Crosstab
  36. Questions from Tableau Training: Creating a Custom Legend as a Sheet
  37. Questions from Tableau Training: How Can I Draw a 45-Degree Angle?
  38. Questions from Tableau Training: Down the Aggregation Rabbit Hole
  39. Questions from Tableau Training: Percent Change from Previous Period on a Map
  40. Questions from Tableau Training: Union File Names as Years
  41. Questions from Tableau Training: Conditional Coloring on Sparklines
  42. Questions from Tableau Training: Moving Column Headers to the Top of the View
  43. Questions from Tableau Training: Isolating Rank with Parameters and Calculations

More About the Author

Jon Bajon

Analytics Consultant
Tableau Viz: How Much Heat Can You Handle? It might sound controversial, but a common practice in my family (and many families throughout Louisiana) was something called ...
Questions from Tableau Training: Can a Top N Parameter be Nested Within Other Fields? The latest question from Tableau Training comes from Ryan, who was in a DII: Intermediate course in New York City. “I know I can ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072