A table is a standard form of data representation with as many details as possible. Tables are most suitable for detailed analysis (a deep dive into figures) and problem detection. It is best to place tables at the end of a dashboard. Graphical representations are simpler for reading information, while tables take you deeper into the data and require more time to read.
Unlike a flat table, categories in a pivot table can be stored both in columns and rows. They may contain multiple categories, while cells at their intersection contain measure values.
Pivot tables make it easier to work with large amounts of data and let you analyze the relationship between different measures. For example, you can use this type of table to analyze product sales depending on the delivery type by brand and product category over a specific year.
|1001 necessary small things
|1001 necessary small things
Sections in the wizard
in the wizard
|Measures. If you add more than one measure to a section, the Columns section contains the Measure Names dimension that defines the location of the measure headers. Measure Names can be moved to Rows.
|Measure. Affects shading of all cells containing indicators. It may only contain one measure.
|Dimensions and measures specified in the Columns and Rows sections.
You can use multiple dimensions and measures.
The order of section fields affects the sorting order of table fields. The sorting direction is marked with an icon next to the field: ascending or descending. To change the sorting direction, click the icon. Sorting by measure only impacts a request to the source and does not affect the pivot table.
|Dimension or measure. Used as a filter.
Creating a pivot table
Not supported in QL charts.
To create a pivot table:
On the DataLens home page, click Create chart.
Under Dataset, select a dataset for visualization.
Select the Pivot table chart type.
Drag a dimension from the dataset to the Columns section.
Drag a dimension from the dataset to the Rows section.
In the Columns and Rows sections, you can change the order of dimensions by dragging them.
Drag a measure from the dataset to the Measures section. The values are displayed in the table cells.
Drag a measure from the dataset to the Color section. Cells with the measure are filled in with a color from the color gradient, depending on the measure value.
Renaming table columns and rows
- Under Columns or Rows, click the icon to the left of the dimension name.
- In the window that opens, change the Name field value and click Apply.
Setting up the width of table columns and rows
In the top-right corner of the Columns or Rows section, click
(the icon appears when you hover over the section).
In the Column and row width window that opens, set up the width of columns and rows:
Auto: Automatic column/row width.
%: Column/row width as a percentage of the table's total width.
px: Column/row width in pixels.
pxoptions let you make a table cell break (by word). This may increase the number of rows in a cell.
The total width of a table always takes up 100% of available space regardless of the specified width of individual columns and rows.
To set the width of any column to
Auto, click Reset.
Adding rows with subtotals
- Under Columns or Rows, click the icon in front of the field name.
- In the field settings window, enable Sub-totals.
- Click Apply.
The table will show columns and/or rows with Total .
To output the common Total row, enable Sub-totals in the settings for the first fields under Columns and Rows.
Setting up a field fill color
Under Columns, Rows, or Measures, click the icon to the left of the field name.
In the window that opens, enable Column fill color.
In the By field list, select the field whose values the fill will be based on.
Set the Fill type:
You can use the Palette type for dimensions and the Gradient type for measures.For a dimensionFor a measure
- Click the color scheme selection field and set a color for each dimension value.
- Click Apply.
Click the gradient selection field and set up:
Gradient type: Select 2 or 3 colors.
- Gradient color: Select a color palette for the gradient from the list.
- Gradient direction: Change the gradient direction using the
Set threshold values: Set numeric thresholds for each color.
Adding a linear indicator to a column with a measure
Under Measures, click the icon to the left of the measure name.
In the window that opens, enable Linear indicator.
Specify the indicator settings:
Sample linear indicator settings
- Fill type: Type of fill color for the indicator.
- Positive values: Indicator color for positive values.
- Negative values: Indicator color for negative values.
- Show labels: This option enables displaying measure values in a cell.
- Show in totals: This option enables displaying the indicator in cells with totals.
- Align: Left or right alignment of the indicator position in a column. Only applies if all numbers in a column are either positive or negative.
- Scale: Sets the indicator scale. If you set it manually, specify the min and max values. Make sure the min value is less than or equal to
0and the max value is larger than or equal to
Use a pivot table to represent aggregate data in table format.
Place dimensions on the left and measures on the right. This makes the data easier to comprehend.
Make sure column names you use are short and easy to read.
Limit the size of your tables or use filters/sorting. Tables with too many rows or columns are hard to read.
You can color table cells depending on the values of a measure. This will help you to highlight the values.
Use tables for their intended purpose only. Do not replace all data visualization types with them.
When posting a table on a dashboard, enable auto height in the widget settings. This will help you save dashboard space.Setting up auto height
If you use a filter, the table height will automatically adapt to the number of rows.Using a filter with the auto height option enabled
If no value is set in the filter, a table displays all rows depending on the limit to the number of rows per page.
If the number of displayed rows decreases when using the filter, the table height is reduced automatically.
Subtotal amount of sales by product category: the
CategorySalesmeasure with the formula
SUM(SUM([Sales]) WITHIN [ProductCategory]).
Total sales: the
TotalSalesmeasure with the formula
SUM(SUM([Sales]) TOTAL).Sample table
Maximum order count per month grouped by product category: the
MaxCountByCategorymeasure with the formula
MAX(COUNTD([OrderID] INCLUDE [ProductCategory])).Sample table
Use sorting. This makes the data easier to comprehend.
Use the URL function in table cells to enable users to follow a link.
When displaying numeric data, specify units and the number of decimal places. For example, if you select
Millions, Min the drop-down list of the Units field, the
10.3 Mvalue is displayed instead of
10,345,234.23. If you set the
Precisionfield value to
123.12is displayed instead of