Quick BI:Fact Table

Prerequisites

You must have already created a dashboard. For more information, see Create a dashboard.

Chart Overview

• Scenarios

  Fact tables are used in scenarios that require detailed data analysis to display metrics within dimensions, facilitating decision-making through analysis of distribution and ranking.

• Advantages

  • Computing power: Easily configure advanced calculations such as period-over-period comparison, cumulative calculations, and totals/subtotals.

  • Visualization: Customize table themes, tree display, cell freezing, auto line wrap, column width, and other display styles.

  • Annotation capabilities: Add custom text, metrics, endnotes, and external links to interact with other systems.

• Sample Effect Diagram

  

Chart Data Configuration

A fact table is composed exclusively of value columns, which can include both dimensions and measures. You can add fields for dimensions along with for measures.

1. On the Data tab, select the desired dimension fields and measure fields:

   • In the Dimension list, find Region and Order Date (year), and either double-click or drag them to the Value columns/dimensions or measures area.

   • In the Measure list, find Order Amount, Unit Price, Shipping Cost, and Profit Amount, and either double-click or drag them to the Value columns/dimensions or measures area.

2. Click Update. The system will automatically refresh the chart.

   

Chart Style Configuration

The following instructions detail how to configure the style of a chart. For general chart style configurations, see Configure the Chart Title.

Use the Search box at the top of the configuration area to quickly find configuration items. Additionally, click the icon on the right to Expand/Collapse all categories or toggle between New/Old fact tables.

• Under Basic table styles, set the appearance of the fact table.

  

  Chart configuration item	Description
  Custom table theme	Set the table theme. You can choose from Zebra stripe, Wireframe, Simplified, Minimalist. For the main color scheme, you can select Theme color, Gray, or Custom.
  Text	Configure the text style of the cross table.
  Column width	Set the Column width mode. You can choose Adaptive to container or Custom. Note The column width settings for PC and mobile are independent. You can configure them separately based on actual requirements.
  Auto wrap	Enable this option to allow table content to wrap automatically. Note The fact table supports recognizing line feed characters in fields. You can also customize the line wrapping effect using line feed characters.
  Dynamic adaptive table height, Maximum height	Table height settings support two policies: "Fixed height" and "Adaptive height". Fixed height: The table height defaults to the "Fixed height" policy. You can manually set the table container height by dragging. Adaptive height: Enable dynamic adaptive height for the table. The table container height adjusts automatically based on the data content. You can also set the maximum adjustable height for the table. When the maximum height is set, the table height will not exceed the specified value during dynamic adaptation. The selectable maximum heights include the following: 192px (approximately 5 rows), 352px (approximately 10 rows), 672px (approximately 20 rows), 1632px (approximately 50 rows), and Custom. The maximum height options include the following: 192 px (approximately 5 lines), 352 px (approximately 10 lines), 672 px (approximately 20 lines), 1632 px (approximately 50 lines), and custom. Note You can configure the height adaptation settings for PC and mobile separately by switching the icon at the top of the page. By default, the dynamic adaptive height feature is enabled for mobile, and only the maximum height value can be adjusted. This feature may affect the overall layout of the report. Enable it based on actual requirements.

• In the Cells section, determine the display and sorting methods for cells.

  

  Chart configuration item	Description
  Freeze	Configure whether to freeze the Table header or Columns.
  Metric grouping	Enable Metric grouping. When Mixed dimension/measure is enabled, dimension fields can also be grouped. Additionally, you can configure field descriptions for the groups.
  Sorting mode	Configure the Sorting mode for the cross table. All methods: Display all sorting methods in the form of a drop-down list. The list shows the predefined sorting methods, and you can manually switch to other methods. Global sorting only: If you want to perform Global sorting for a field by Clicking the column heading, select this mode. After enabling, click the Sort icon on the right side of the column heading to select Ascending/Descending order.
  Merge similar cells	Choose whether to merge similar cells.
  Ordinal column	Enable this option to display ordinal numbers in the column header. ① Configure the name of the ordinal column. ② Enable Do not display ordinal numbers in total rows. Note The option Do not display ordinal number in total row is supported only when the column totals are selected in the cross table.
  Pager	Click the pager icon to activate the pager editing page. The default selection for the paginator pattern is the regular pattern. When the data volume is too large, it is recommended that you select the simple pattern. In this mode, the total number of data entries will not be calculated. The supported paging options are 10 items/page, 20 items/page, 30 items/page, 50 items/page, 80 items/page, 100 items/page, 200 items/page, and 500 items/page. The supported pagination options are 10 entries/page, 20 entries/page, 30 entries/page, 50 entries/page, 80 entries/page, 100 entries/page, 200 entries/page, and 500 entries/page.

• In Table header, customize the style of the row and column headers in the cross table.

  

  Configuration item	Description
  Do not display column headers	Enable this option to hide the column header content.
  Background fill	Set the background fill color for the column headers.
  Text	Configure the text style of the column headers.
  Set alignment	Configure the alignment of the column headers. You can choose left alignment, center alignment, or right alignment.

• In Conditional formatting, set up rules for data formatting based on conditions.

  

  Chart configuration item	Description
  Select Related Fields	Select the fields for which you want to configure conditional formatting. All fields added to the data panel area can be selected. After completing the style configuration, if you no longer need certain configured conditional formats, you can click Batch clear format at the bottom right of the field panel. In the selection list, choose the conditional formats to clear, and click OK to complete the clearing.
  Quick Styles	Set the marker icon or font display color for the current field from the system-provided styles. Quick styles can only be configured for measure fields, not for dimension fields. Comparison: Choose Compare with 0 or Compare with mean. Two-color fill: Choose the color of the data bar. Note Data bar length calculation: When the maximum and minimum values are one positive and one negative, the denominator is the absolute value of the larger number. When both the maximum and minimum values are positive or negative, the denominator is the absolute difference. Color scale: Choose Gradient, Two-step, or Three-step. Color inversion Take comparison as an example. Less than 0 is red and greater than 0 is green. After flipping, less than 0 becomes green and greater than 0 becomes red. For example, in comparison, values less than 0 are red and values greater than 0 are green. After inversion, values less than 0 are green and values greater than 0 are red. Clear: If you no longer need a configured quick style, click Clear to remove the style.
  Custom Styles	In addition to the system-provided quick styles, you can configure custom styles as needed. Both measure fields and dimension fields support custom styles, but the configurable content differs. Measure fields: Configure Highlighted text/Background, Icons, Color scale, and Data bars. Highlighted text/Background In Rule definition: Set the conditions for highlighting the text of the current measure field. You can control which data field the rule applies to by configuring the Field option. By default, the field selected in the configuration is displayed. You can also select other fields to define more complex conditional rules based on the values of other fields. In More settings: Configure whether to hide the text of the current field and whether to apply the background color to the entire row. Icons In Rule definition: Set the icon marking rules for the current measure field. You can control which data field the rule applies to by configuring the Field option. By default, the field selected in the configuration is displayed. You can also select other fields to define more complex conditional rules based on the values of other fields. In More settings: Configure whether to hide the text of the current field. Color scale In Rule definition: Set the display rules for the color scale of the current measure field. In More settings: Configure the shape of the color scale bar and whether to hide the text of the current field. Note Color scale configuration is supported only when Highlighted text/Background, Icons, and Data bars are not selected. Data bars In Rule definition: Set the display rules for the data bars of the current measure field. In More settings: Configure whether to hide the text of the current field. Dimension fields: Configure Highlighted Text/background and Icons. Highlighted Text/background In Rule definition: Set the conditions for highlighting the text of the current dimension field. In More settings: Configure whether to hide the text of the current field. Icons In Rule definition: Set the icon marking rules for the current dimension field. In More settings: Configure whether to hide the text of the current field.
  Conditional styles applied to summary data	Enable this option to apply the configured conditional styles to summary data as well.
  Synchronize Styles To	Synchronize the current conditional formatting to other fields.

• In Totals/Subtotals, adjust the summary style for Column Totals.

  

  For frequently asked questions about configuring totals, see FAQ about Totals.

• In the Filters section:

  • Activate Field Content Filtering. In preview mode, use the Selected Fields dropdown on the cross table to filter or sort fields.

  • Once Enable Quick Filter is selected, you can use the Filter dropdown in preview mode on the cross table to filter fields.

    

• In Auxiliary display, configure the Scrollbar display mode and Scrollbar thickness.

  • Set the display mode to Show on mouse hover to reveal the scrollbar only when the mouse hovers over the table.

  • For a constantly visible scrollbar, choose Always visible.

  • To make the scroll bar more visible, you can increase its thickness.

    

Chart Analysis Configuration

Display Secondary Indicators

Once measures are configured, you can enable and set up secondary indicators.

Activate Secondary indicators. The comparison indicator dropdown allows selection from all measures.

• Comparison date selection: Choose a date field from the dataset.

• Comparison method: The comparison content displayed is contingent upon the granularity of the date field, with up to four comparison fields available. For instance, when the Comparison date is set to daily granularity, the Comparison content will show options such as Compare (day to day), Compare (week on week), Compare (month on month), and Compare (year on year). Conversely, if the Comparison date is set to monthly granularity, the Comparison content will include options like Compare (month on month) and Compare (year on year).

• Display content: By default, Percentage difference is selected. You can also enable Difference value or Original value.

  • Difference value: The data format aligns with the metric's display format.

  • Percentage difference: The format retains two decimal places.

• Percentage difference calculation in pt: This option is not enabled by default. When activated, if the difference value is selected and the comparison indicator is a percentage metric, it will be displayed in pt. For example, 5% - 4% equals 1 pt.

• Display position: The default is Right of the metric. You can also select Independent right or Below the metric.

  • Right of the metric: Adds a new column of indicators to the right of the current metric. The column name reflects the selected comparison method, such as Compare (year on year), Compare (day to day), Compare (month on month), or Compare (week on week).

    When the metric is in the column header, secondary indicators appear to the right of the metric.

  • Independent right: Stands alone to the right of the current metric.

    • If the metric is in the column header, the last column dimension is split to display Compare (day to day) and Compare (week on week). For example, the dimension value All is divided into All, All (day to day), and All (week on week). Two indicators are shown under each dimension value.

    • If the metric is in the row header, the last row dimension is split to display Compare (day to day) and Compare (week on week). The difference is that secondary indicators are shown below the metric instead of to the right.

  • Below the metric: Adds multiple rows of comparison indicators beneath the current metric. The alignment matches that of the metric, with text left-aligned and values right-aligned.

    The comparison indicators occupy the same row cell as the current metric. A new row is added below the column header name of the current metric, labeled with the selected comparison method, such as Compare (day to day), Compare (week on week), Compare (month on month), Compare (year on year), etc.

• The default value is determined by the current environment. If the environment is Chinese, select Positive num show red and negative num show green. If the environment is English, choose Positive num show green and negative num show red. Once configured, the color is fixed based on the selection, regardless of the environment.

• Use Synchronize styles to to apply the current metric's configuration to N metrics.

FAQ about Totals

Explanation of total calculations

• If the original data is GMV (sum), the default calculation method for totals and subtotals is Automatic. The logic for totals and subtotals is GMV (sum). If you switch the total to Custom and choose Average as the aggregation method, the logic for the total becomes GMV (average).

• If the original data is id (remove duplicates), the default calculation method for totals and subtotals is Automatic. The logic for totals and subtotals is id (remove duplicates). If you switch to Custom and select Count as the aggregation method, the logic for the total changes to id (count).

What is the calculation logic for ratios?

1. First, define the ratio.

   For example, let's define the ratio as (Metric A/Metric B).

2. Next, create a calculated field in the dataset with the expression: rate = sum(A)/sum(B).

3. In the dashboard's data panel, select the metric rate (sum) and enable totals and subtotals. The default logic is rate (sum), which calculates totals and subtotals as sum(A)/sum(B).

   Note

   Note that if the expression in the dataset is rate = A/B, and you directly enable totals and subtotals with the automatic logic A/B (sum), you may encounter an abnormal situation where the ratio exceeds 100%, indicating an incorrect calculation.