Table
A table is a standard form of data representation with as many details as possible. It stores data as a two-dimensional array that consists of columns and rows. Table column headers show dimension or measure names, while cells contain their values. Each row is a set of cells with each column's value. A table may also contain a row with results.
Tables are a great tool for detailed analysis (a deep dive into figures) and problem detection. At the same time, it takes longer to read information from a table than from a graph or chart. Therefore, it is a good idea to place it at the end of a dashboard.
A table is a good choice for quantitative comparison, where you can see different values of multiple categories or dimensions. The table below shows three different measures by three categories.
Categories | Subcategories | Delivery type | Sales | Number of customers | Number of orders |
---|---|---|---|---|---|
Household cleaners | Detergents | Delivery | 597729 | 250 | 256 |
Household cleaners | Detergents | Pickup | 1352690 | 521 | 550 |
Household cleaners | Cleaners | Delivery | 541911 | 225 | 230 |
Household cleaners | Cleaners | Pickup | 1348868 | 493 | 518 |
Household goods | Beauty and health products | Delivery | 263891 | 132 | 134 |
Household goods | Beauty and health products | Pickup | 706423 | 321 | 333 |
Household goods | Kitchen products | Delivery | 671566 | 165 | 167 |
Household goods | Kitchen products | Pickup | 1283731 | 289 | 297 |
Household goods | Non-essential goods | Delivery | 701818 | 210 | 213 |
Household goods | Non-essential goods | Pickup | 1521937 | 447 | 475 |
Home appliances | Kitchenware | Delivery | 829157 | 213 | 218 |
Home appliances | Kitchenware | Pickup | 2048211 | 482 | 513 |
Home appliances | Health and beauty equipment | Delivery | 944770 | 255 | 258 |
Home appliances | Health and beauty equipment | Pickup | 2387410 | 550 | 581 |
To simplify the comparison of different values and make the information easier to comprehend, you can use advanced settings when creating a table:
- Renaming table columns.
- Setting up table data sorting by multiple columns.
- Setting up the width of table columns.
- Adding a row with totals.
- Adding row color.
- Setting up a field fill color.
- Adding a linear indicator to a column with a measure.
Sections in the wizard
Wizard section |
Description |
---|---|
Columns | Dimensions and measures to be used as columns. Field names are used as column headers. |
Colors | Measure. Affects shading of all cells within a row. It may only contain one measure. |
Sorting | Dimensions and measures specified in the Columns section. 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: |
Filters | Dimension or measure. Used as a filter. |
Creating a table
To create a table:
-
Go to the DataLens home page
. -
In the left-hand panel, select
-
Click Create chart → Chart.
-
At the top left, click
-
Select the Table chart type.
-
Drag a dimension or measure from the dataset to Columns. The field is displayed as a column.
Note
- After dragging a Tree of strings dimension to the Columns section, you will see a tree hierarchy in the visualization area.
- You can change the order of columns by dragging dimensions in the Columns section.
Additional settings
Renaming table columns
- Under Columns, click the icon to the left of the dimension or measure name.
- In the window that opens, change the Name field value and click Apply.
Setting up table data sorting by multiple columns
- On the left side of the screen above the chart, click
- Enable the Pagination setting and click Apply.
- Press and hold Ctrl while clicking the headers of columns to change the sorting for.
Setting up the width of table columns
-
In the top-right corner of the Columns section, click
-
In the Column width window that opens, set up the width of each column:
Auto
: Automatic column width.%
: Column width as a percentage of the table's total width.px
: Column width in pixels.
The
%
andpx
options let you make a table cell break (by word). This may increase the number of rows in a cell.Sample column width settingsNote
The total width of a table always takes up 100% of available space regardless of the specified width of individual columns.
-
Click Apply.
To set the width of any column to Auto
, click Reset.
Adding a row with totals
- On the left side of the screen above the chart, click
- Enable the Totals settings.
The Total row is displayed in the table. Values in the row are calculated using the same formulas as aggregation in the measure.
Note
Values in the Total row are calculated only for measures. For dimensions, the row is empty.
Adding row colors
-
Drag a measure to the Color section.
-
In the top-right corner of the Color section, click
-
Set up colors:
- Gradient type: Select two or three 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.
- Gradient type: Select two or three colors.
Setting up a field fill color
-
Under Columns, click the icon to the left of the dimension or measure 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:
Note
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 two or three 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.
-
-
Click Apply.
-
Click Apply.
Adding a linear indicator to a column with a measure
-
Under Columns, click the icon to the left of the measure name.
-
In the window that opens, enable Linear indicator.
-
Specify the 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
0
and the max value is larger than or equal to0
.
Sample linear indicator settings -
Click Apply.
Recommendations
-
Limit the size of your tables or use filters/sorting. Tables with too many rows or columns are hard to read.
-
Use tables for their intended purpose only. Do not replace all data visualization types with them.
-
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 readable.
-
Enable the display of totals at the bottom of a table. If the Pagination option is selected, the
Total
row is placed on the last page.Table with totals and pagination -
When posting a table on a dashboard, enable auto height in the widget settings. This will help you save dashboard space.
Setting up auto heightIf you use a filter, the table height will automatically adapt to the number of rows.
Using a filter with the auto height option enabledIf 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.
-
Represent totals (or subtotals) as a column. To do this, use calculated fields based on window functions or LOD expressions. For example:
-
Subtotal amount of sales by product category: the
CategorySales
measure with the formulaSUM(SUM([Sales]) WITHIN [ProductCategory])
. -
Total sales: the
TotalSales
measure with the formulaSUM(SUM([Sales]) TOTAL)
.Sample table -
Maximum order count per month grouped by product category: the
MaxCountByCategory
measure with the formulaMAX(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.
-
If a table cell contains a long text, set up the column width to enable a row break in cells.
Setup example -
When displaying numeric data, specify units and the number of decimal places. For example, if you select
Millions, M
in the drop-down list of the Units field, the10.3 M
value is displayed instead of10,345,234.23
. If you set thePrecision
field value to2
, then123.12
is displayed instead of123.1234
.