The pivot table presents dimensions and measures as rows and columns in a table. In a pivot table you can analyze data by multiple measures and in multiple dimensions at the same time.
You can rearrange measures and dimensions by pivoting rows and columns.
When to use it
The pivot table is useful when you want to include several dimensions or measures in a single table, and then want to reorganize them to see different subtotals.
Advantages
The pivot table is very powerful when you want to analyze multiple dimensions and measures at once, and then reorganize them to get a different perspective on your data. You can expand the rows you are interested in while keeping the rows in the rest of the table collapsed.
Disadvantages
The pivot table may seem a bit complicated, and does not give insights at a glance.
Creating a pivot table
You can create a new pivot table on the sheet you are editing.
Gehen Sie folgendermaßen vor:
- From the assets panel, drag an empty pivot table to the sheet.
- Klicken Sie auf Dimension hinzufügen und wählen Sie eine Dimension oder ein Feld aus.
- Klicken Sie auf Hinzufügen und wählen Sie eine Kennzahl aus oder erstellen Sie eine Kennzahl aus einem Feld.
You can adjust appearance and other settings in the properties panel. For information about styling, see Styling the pivot table.
Styling the pivot table
Unter Darstellung im Eigenschaftsfenster sind mehrere Designoptionen verfügbar.
Klicken Sie auf 
Design unter Darstellung > Präsentation, um das Design des Diagramms weiter anzupassen. Das Designfenster enthält mehrere Abschnitte auf den Registerkarten Allgemein und Diagramm.
Sie können Ihre Designs zurücksetzen, indem Sie auf 
neben den einzelnen Abschnitten klicken. Wenn Sie auf Alle zurücksetzen klicken, werden die Designs für alle verfügbaren Registerkarten im Designfenster zurückgesetzt.
For general information about styling an individual visualization, see Applying custom styling to a visualization.
Anpassen des Texts
Sie können den Text für Titel, Untertitel und Fußnoten unter Darstellung > Allgemein festlegen. Um diese Elemente auszublenden, deaktivieren Sie Titel anzeigen
Die Sichtbarkeit der verschiedenen Bezeichnungen des Diagramms hängt von den diagrammspezifischen Einstellungen und den Anzeigeoptionen der Bezeichnungen ab. Diese können im Eigenschaftsfenster konfiguriert werden.
Sie können das Design des im Diagramm angezeigten Texts ändern.
Gehen Sie folgendermaßen vor:
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
Legen Sie auf der Registerkarte Allgemein die Schriftart, den Hervorhebungsstil, die Schriftgröße und die Farbe der folgenden Textelemente fest:
-
Titel
-
Untertitel
-
Fußnote
-
-
On the Chart tab, set the font size and color for the following text elements:
- Header: Style the text of the headers for each column to the right of the first column.
- Content: Style the text of the first column, as well as each cell in the table itself.
Additionally, you can customize how the text appears when a user hovers over a row. See Customizing the hover behavior and scrollbar.
Customizing the background
You can customize the background of the chart. The background can be set by color and image.
Gehen Sie folgendermaßen vor:
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
On the General tab of the styling panel, you can select a background color (single color or expression). You can also set the background to an image from your media library or from a URL.
When using a background color, use the slider to adjust the opacity of the background.
When using a background image, you can adjust image sizing and position.
If a cell in the pivot table has a null value, it is colored separately from the background settings applied in the styling panel (it appears in a white color).
Customizing the hover behavior and scrollbar
Sie können Anzeigeoptionen festlegen, wenn ein Benutzer den Mauszeiger über eine Zeile in der Tabelle bewegt. Sie können auch die Größe der Scrollleiste festlegen.
Gehen Sie folgendermaßen vor:
-
Erweitern Sie im Eigenschaftsfenster den Abschnitt Darstellung.
-
Klicken Sie unter Darstellung > Präsentation auf
Design. -
Passen Sie auf der Registerkarte Diagramm des Designfensters unter Beim Draufzeigen die folgenden Einstellungen an:
-
Um Zeile in der Tabelle hervorzuheben, wenn ein Benutzer mit dem Mauszeiger daraufzeigt, legen Sie den Schalter auf Ein um. Deaktivieren Sie das Verhalten je nach Belieben.
-
Farbe beim Daraufzeigen: Legen Sie die Farbe fest, mit der die Zeile hervorgehoben wird, wenn ein Benutzer den Mauszeiger darüberbewegt.
-
Schriftfarbe beim Daraufzeigen: Legen Sie die Farbe des Texts in der hervorgehobenen Zeile fest, wenn ein Benutzer den Mauszeiger darüberbewegt.
-
-
Legen Sie unter Größe der Scroll-Leiste die Größe der Scroll-Leiste im Diagramm fest (Sie können Klein, Mittel oder Groß auswählen).
Customizing the border and shadow
You can customize the border and shadow of the chart.
Gehen Sie folgendermaßen vor:
-
In the properties panel, expand the Appearance section.
-
Under Appearance > Presentation, click
Styling. -
On the General tab of the styling panel, under Border, adjust the Outline size to increase or decrease the border lines around the chart.
-
Select a color for the border.
-
Adjust the Corner radius to control the roundness of the border.
-
Under Shadow in the General tab, select a shadow size and color. Select None to remove the shadow.
Pivoting your data in the table
When you want to rearrange your data, drag the items to a new column or row.
In the following pivot table, the dimension Customer has been dragged to the position after Product Group. The dimension Item Desc has been moved to the position before Product Group. As a consequence, the dimensions are now sorted by Item, primarily. Focus has shifted from Customer to Item Desc. You can find out the quantities and sales for each customer by clicking 
. This will expand a dimension.
By moving the dimension Customer from rows to columns, you retain focus on the dimension Item Desc, but you also get the distribution of items per customer. The move has made the pivot table more information dense.
Measure grouping
As you may have noticed, Quantity and Sales are not presented as separate measures in the top column row. Next to the dimension Customer, you find an item called Values. When you use more than one measure, they are automatically grouped together forming a measure group, Values. This group can be added to the rows section or the columns section. The measure group cannot be edited or selected in the table. You cannot split up the measure item and use one measure as a row and another as a column.
You can use the properties panel to sort by the first measure in the group, see Sort by first measure.
Pivoting your data in the properties panel
In the properties panel, you can add measures and dimensions to the pivot table, and also pivot rows or columns.
Data
In the data pane, you can add dimensions and measures. You can move items between rows and columns. You can also change item order inside rows or columns. When you use more than one measure, they are grouped and a Values item is created.
Sorting
On the sorting pane, you can change the internal order of dimensions and measures.
Sort by first measure
If you have more than one measure, a measure group is created. Turn on Sort by first measure to sort dimension values by the numeric value of the first measure. You can choose between Ascending and Descending sort order.
This sort order will affect all dimensions, and take precedence before any other sort order defined under the dimensions.
For example, you have two measures: Quantity and Sales. In the properties pane, under Data> Measures, Quantity is listed first. If you Sort by first measure in descending order, your table is sorted starting with the dimension with the highest Quantity.
If Sort by first measure is turned off, the table uses the sort order configured for the dimensions.
Pivot table in edit mode. Sort by first measure has been toggled on.
Limitations:
- This option is only supported if subtotals are calculated. You can calculate subtotals by doing one of the following:
- In the properties pane, go to Data, and click on a dimension. Toggle on Show totals.
- In the properties pane, go to Appearance > Presentation. Toggle on Indent rows.
- This option is not supported for calculated dimensions.
- This option is only supported if all dimensions are in the Row section and all measures are in the Column section.
Dimension sorting
With Sort by first measure turned off, the table can be sorted by dimension values. Each dimension can have Auto or Custom sorting. With Auto sorting, field values are sorted alphabetically and numerically in ascending order. With Custom sorting, you can turn each of the following settings on or off:
-
Sort by expression: Sorts by custom expression. Sort by expression overrides the Sort numerically and Sort alphabetically settings. This option should only be used with the first (outermost) dimension in the table.
-
Sort numerically: Sorts field values starting with a number.
-
Sort alphabetically: Sorts field values starting with a letter.
Ascending and Descending options are available for all three settings.
Sorting in search
The user can click a dimension name in the table to expand a listbox. In the listbox, the user can search and select individual dimension values. The Sorting in search setting controls the sort order of the values in a dimension listbox.
You have the following options for the listbox sort order:
-
Auto: Uses the default sort order. Numbers are sorted numerically in ascending order. Text is sorted alphabetically in ascending order.
-
Inherit from dimension: Uses the sort order defined for the individual dimension.
Global grouping
Global grouping lets you create a limited data set, and within that data set, single out values that you want to focus on. For example: the best quarters, the top sales people, or the worst selling products.
Beispiel:
In the following pivot table, no limitation is applied. The values are sorted on Sales, descending. The list is long and the values for 2013 are not shown.
In the following pivot table, a limitation has been applied to the (inner) dimension Sales Rep Name, so that only the top five sales representatives for the years 2013 and 2014 are shown.
The next step is to select global grouping in the properties panel. The option Global grouping is only available after you have applied a limitation on the dimension.
When global grouping is selected, the limitation on the top five sales representatives is applied again, but this time the dimension Year is ignored. The five sales representatives with the highest sales (either in 2013 or 2014) are the only ones that will be presented in the final pivot table.
The following image shows the six highest results for 2014 and 2013. The top four results are from 2014, but the fifth (John Greg) is from 2013. Because five other sales representatives have higher sales than Brenda Gibson (who was number five in 2014), she is removed.
The following image shows the pivot table with global grouping applied. The pivot table only contains the sales results for the top five sales representatives. Even though Brenda Gibson had a better result in 2014 than John Greg, his result for 2013 qualified him for the top five list.
Creating a bookmark with an expanded pivot table
By default, if you create a bookmark that contains a pivot table, the pivot table will be shown collapsed. If you expanded any rows using , they will not be shown. However, you can choose to show the pivot table as expanded.
Gehen Sie folgendermaßen vor:
-
Click
in the toolbar. -
Click Create new bookmark.
Change the name and description, if desired.
- Toggle on Save layout.
- Click Save.