Pivot Tables

Pivot Tables

Introduction

This pivot table of grouped values shows the sums, averages, or other statistics of all the individual items in a more extensive table. Pivot tables can be created by using pivot charts to rearrange information.

|   Pivot tables, take the rows in a standard table and pivot them so they become columns. This lets you group and summarize the data in ways a standard table can't provide.

Example:

The following is a standard table listing the Revenue Per User metric by calendar quarter and year:

 

While this table is useful for showing which country received the most revenue per user and in which quarter, it isn't useful for summarizing this data in a meaningful way.

A pivot table, however, quickly shows the relationship of this data:

This table easily summarizes the data from the previous example. You can also quickly spot outliers or anomalies in your data. Notice that several countries had no revenue in Q4, for example.

Pivot tables in Data Studio support adding multiple row and column dimensions. The example below adds the Gender dimension to the rows. This further breaks down the data, giving you even more insight into your data:

 

Advantages

  1. Show totals: Pivot tables support totals and subtotals for both rows and columns:

Example pivot table showing totals and subtotals.

  1. Expand-collapse: Expand-collapse lets report viewers show or hide different levels of information in the pivot table by clicking + and  in the column header. Viewers can then explore the data at the level of detail that interests them most. Expand-collapse also provides a way for a single pivot table to show both summary and detailed information, reducing the number of charts needed in your reports.

How to add a pivot table to your report?

Chart Settings

Click on the chart, afterwards on the right, use the properties panel to change the chart options.

Data Tab

This tab shows settings related to the data source.

  • Data source: A data source lets you to connect to the data set you want to use. Data source options are: change the data source, view/edit the data source, add a blended data source.

  • Dimension: Dimensions are categories for your data, and dimension values are a name, description or other characteristic of a category. You can choose dimensions either directly from the available fields or by clicking the "Add Dimension" link. Dimensions may differ according to cart types. For example geo charts's dimensions will be only locations.

    • Row dimension: The row dimensions provide the breakdown of rows in the pivot table. Reorder the dimensions listed to change the order of the rows in your table.
    • Expand-collapse: Turn on expand-collapse to treat the row dimensions as an expandable hierarchy
💡 The order in which you list the dimensions within the hierarchy matters. As a rule of thumb, you should define hierarchies to always go from the most general to the most specific. For example, defining a geographic hierarchy as Country > City > Region could produce undesirable results, because you're going from a general level to a more detailed level, then back to a more general lev
    • Column dimension: The column dimensions provide the columns in the pivot table. Reorder the dimensions listed to change the order of the columns in your table.
  • Date range dimension: This option appears if your data source has a valid date dimension. For Google Ads and Analytics data sources, this option is automatically set to the Date dimension. With this, you can set a date range property for this chart, or if a viewer of the report uses a date range control to limit the time frame.
  • Metric: Metrics are used to measure the contents of dimensions and provide the chart with a numeric scale and data series. Drag fields from the Available Fields tab on the right onto the chart to add measurements. You can also click Add metric in the Data tab.

  • Totals: Display totals for each row and column. If you have only 1 dimension in a row or column, the option is to display a grand total. If you have 2 or more dimensions, the options include subtotals and grand totals.

  • Setting the default sort: The Sort and Secondary sort options control the default sorting behavior. You can select any metric in the chart's data source, or any dimension currently displayed in the chart, to use as the primary or secondary sorting field. The Secondary sort option only appears when there is an appropriate combination of dimensions and metrics in the chart.

  • Default date range: This lets you set the timeframe for an individual chart*.* When this is changed, this specific chart's date range overrides the date range of the page. Default date range options:

    • Auto: Applies the date frame of the dashboard if there is a data selector. If not, then tries to include all data.
    • Custom: Lets you use the calendar widget to select a custom date range for the chart.
    • Date compare type: Displays comparison data for the selected time period.
  • Filter : Filter help filtering the raw data which is coming from the data source. It might be helpful when you want to includes specific values or exclude some. For example you might want to exclude ad campaigns with zero impressions.

  • Google Analytics segment: This option appears for charts based on a Universal Analytics data source. A segment is a subset of your Analytics data. You can apply segments to your Data Studio charts to help ensure that your Data Studio and Google Analytics reports show the same data.

  • Interactions : When interactions are enabled on a chart, that charts acts like a filter controls. Here is an example, on this dashboard, campaign table has the interactions option activated. So viewer can click on any row and top metrics represent metrics of this selected row.

 

Style tab

A chart's style properties control the overall presentation and appearance of the chart.

  • Conditional formatting: It is to evaluate a chart against one or more rules that you set. When the rule condition has met, your setting is applied to the data so the user can quickly see what has changed. Click the + Add selection and then decide rules, color and style to apply when the format rules are met.

  • Table header: These options control the appearance of the table header and column labels.

    Show header → Shows or hides the table header. Note that viewers can't sort tables with hidden headers.

    Wrap text → Wraps header text.

    Header font color → Sets the font color of the table header.

    Header font size → Sets the font size of the table header.

    Header font family → Sets the font family of the table header.

  • Table colors: These options control the colors of the table borders and cells.

    Header background color → Sets the color of the table header background.

    Cell border color → Sets the color of the border between rows.

    Odd/Even row color → Sets the color of odd or even rows in the table.

  • Table labels: These options control the appearance of the table data.

    Font color → Sets the font color of the data.

    Font size → Sets the font size of the data.

    Font family → Sets the font family of the data.

    Heatmap text contrast → Sets the font color automatically when displaying a heatmap. Choose from 3 levels of contrast, low, medium, or high.

  • Missing Data: This option is visible when your dimension has a missing data. There are 3 options: line to zero, line breaks, linear interpolation (Google Data Studio connects 2 points before and after the missing point)

  • Metric: This section controls the appearance of the metrics.

    • Drop-down menu
      • Number - displays the metric value "as is."
      • Heatmap - displays the metric value with a colored background, the intensity of which shows how that value compares to the other values in that column.
      • Use the Heatmap text contrast option (in the Table Labels section above) to set the font color automatically to provide better readability of your data labels. Choose from 3 levels of contrast: Low, Medium, or High.
      • Bar - displays the metric value as a horizontal bar. You can change the bar color and include the numeric value, as well, if desired.
    • Compact Numbers: Rounds numbers and displays the unit indicator. E.g., 553,939 becomes 553.9K.
    • Decimal Precision: Sets the number of decimal places in metric values.
    • Show target: Appears when the column type is set to Bar. Shows a target line for the value set in the target value field, similar to a bullet chart.
    • Show axis: Displays an X-axis for the bar chart.
  • Background and border: These options control the appearance of the chart background container.

    • Background: Sets the chart background color.
    • Border Radius: Adds rounded borders to the chart background. When the radius is 0, the background shape has 90° corners. Border radius of 100° produces a circular shape.
    • Opacity: Sets the chart's opacity. 100% opacity completely hides objects behind the chart. 0% opacity makes the chart invisible.
    • Border Color: Sets the chart border color.
    • Border Weight: Sets the chart border line thickness.
    • Border Style: Sets the chart border line style.
    • Add border shadow: Adds a shadow to the chart lower and right borders.
  • Chart Header: The chart header lets viewers perform various actions on the chart, such as exporting the data, drilling up or down, and viewing the chart in the Explorer tool. You can choose show on hover - which is default, always show and do not show.

Photo by Mika Baumeister on Unsplash

Blog post

Give your customers a summary of your blog post