Quick BI:Fact Table

Chart Overview

• Scenarios

  You can use fact tables for analysis scenarios that require displaying detailed measure data across dimensions. They enable data-driven decision-making by providing detailed data analytics from different perspectives, such as distribution and ranking.

• Advantages

  • Calculation capabilities: You can configure advanced calculations, such as period-over-period comparisons, cumulative calculations, and totals and subtotals, with a single click.

  • Visualization effects: You can configure table themes, freeze panes, wrap text, and set column widths.

  • Annotation capabilities: You can add custom text, measure notes, and endnotes. You can also define external links to enable interaction between your data and other systems.

• Sample Chart

  

Limits

• Prerequisites: You have created a dashboard. For more information, see Create a dashboard.

• To ensure proper configuration and an effective display of fact tables, note the following field and data volume limitations:

  • A fact table consists only of value columns, dimensions, or measures. You can add dimension fields and measure fields.

Configure Chart Data

A fact table consists of value columns, dimensions, or measures. You can add dimension and measure fields to it.

1. On the Data tab, select the dimension and measure fields that you need.

   • In the Dimensions list, find Region and Order Date (year). Double-click or drag them to the Value Column/Dimension or Measure area.

   • In the Measures list, find Order Amount, Unit Price, Shipping Cost, and Profit Amount. Double-click or drag them to the Value Column/Dimension or Measure area.

     Note

     If a field is configured as an image, you can set its display size. By default, the width and height of the image on a mobile device are half of the size set for a PC.

2. Click Update. The system automatically updates the chart.

   

Configure the Chart Style

This section describes how to configure the chart style. For information about common style settings, see Configure the chart title.

You can enter a keyword in the Search box at the top of the configuration section to find a configuration item. You can also click the icon on the right to expand or collapse all categories and switch between the New and Old fact table styles.

• In the Table Basic Style section, set the style of the fact table.

  

  Configuration item	Description
  Custom Table Theme	Set the appearance of the fact table. Theme Style: Select Default, Border, Simple, or Minimalist. Main Color Scheme: If the table theme is set to Default, Border, or Simple, you can select a main color scheme for the fact table. Options include following the Theme Color, Gray, or Custom. Display Zebra Stripes: Choose whether to display zebra stripes in the fact table and set their color.
  Text	Set the text style for the fact table.
  Column Width	Set the Column Width. You can select Automatic Fit or Custom. Note The column width settings for PCs and mobile devices are independent. You can set the column width for PCs or mobile devices separately as needed.
  Auto Wrap	Select this option to automatically wrap the table content. Note Fact tables can detect line feeds in fields. You can also use line feeds to customize line break effects.
  Dynamic Table Height Adaptation, Maximum Height	Table height settings support two policies: Fixed Height and Adaptive Height. Fixed Height: By default, tables use the Fixed Height policy. You can manually set the table container height by dragging it. Adaptive Height: Enable dynamic table height adaptation to automatically adjust the table container height based on the data content. You can also set a maximum height for the table. When set, the table height does not exceed this value during dynamic adaptation. Supported maximum heights include 192 px (about 5 rows), 352 px (about 10 rows), 672 px (about 20 rows), 1632 px (about 50 rows), and custom. Note Click the icon at the top of the page to configure height adaptation settings for PCs and mobile devices separately. On mobile devices, dynamic table height adaptation is enabled by default. You can only adjust the maximum table height. This feature may affect the overall layout of the report. Enable it as needed.

• In the Cell section, you can set the cell display, sorting, and other methods.

  

  Configuration item	Description
  Freeze	Set whether to freeze the table header or columns.
  Metric Display Grouping	Set Metric Grouping Display. After you enable Mixed Dimension/Measure Layout, dimension fields can also be grouped. Groups also support field descriptions.
  Sort Mode	Set the sort mode for the fact table. All methods: Displays all sorting methods in a dropdown list. You can manually select and switch the sorting method for a column in the dropdown list. Global sort only: If you want to perform a global sort on a field by clicking the column heading, select this option. After you select this option, you can click the icon on the right of the column heading to choose ascending or descending order. Global sorting supports multi-column and single-column sorting. If you select Multi-column, you can sort the data based on multiple columns at the same time. The system first sorts the data by the first selected column. If there are identical values, it then sorts by the second selected column, and so on. If you select Single-column, the chart is sorted based on a single column. All rows are rearranged based on the values in that column. This means that only one column can have sorting logic applied at a time. For example, if you sort Column A and then sort Column B, the sorting on Column A is automatically cleared. No sorting: Sorting methods cannot be set. If you select this option, report viewers cannot sort data in any column of the chart.
  Merge Similar Cells	You can choose to merge or not merge similar cells.
  Auto Wrap	Select this option to automatically wrap the table content. Note Fact tables can detect line feeds in fields. You can also use line feeds to customize line break effects.
  Image Field Display Mode	If the chart contains image fields, you can set the display mode for the image fields. Options include scaling while maintaining the original aspect ratio, cropping to fit the area while maintaining the original aspect ratio, or stretching to fit the area. Note You can use the Image Field only when the dataset contains an image field and its field type is image ().
  Ordinal Number Column	Select this option to display ordinal numbers in the report column header. ① You can set the name of the ordinal number column. ② You can select Do not display ordinal numbers in total rows. Note The Do not display ordinal numbers in total rows option is available only when column totals are enabled for the fact table.
  Paging	Click the paging icon to open the paging editing page. The default paging mode is Normal. If you have a large amount of data, we recommend that you select the Simple mode. This mode does not calculate the total number of data records. 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.

• In the Table Header section, set the style of the row and column headers in the fact table.

  

  Configuration item	Description
  Hide Column Headers	Select this option to hide the column headers.
  Background Fill	Set the background fill color for the column headers.
  Text	Set the text style for the column headers.
  Set Alignment	Set the alignment for the column headers. You can select left, center, or right alignment.

• In the Conditional Format section, set the conditional format for data.

  

  Configuration item	Description
  Select Field	Select the field for which you want to configure conditional formatting. You can select any field that has been added to the data panel. After you configure the style, if you no longer need some of the configured conditional formats, you can click Batch Purge Format in the lower-right corner of the field panel. Select the conditional formats that you want to clear from the list and click OK.
  Quick Style	Set the icon or font color for the current field from the styles provided by the system. Quick styles can be configured only for measure fields, not for dimension fields. Comparison: You can select Compare with 0 or Compare with Average. Two-color Fill: Select the color of the data bar. Note Data bar length calculation method: If the maximum and minimum values have opposite signs (one positive, one negative), the denominator is the maximum absolute value of the two. If the maximum and minimum values have the same sign (both positive or both negative), the denominator is the difference between their absolute values. Color Scale: You can select Gradient, Two-level, or Three-level. Flip Colors For example, in a comparison, values less than 0 are red and values greater than 0 are green. After you flip the colors, values less than 0 become green and values greater than 0 become red. Delete: If you no longer need a quick style that has been set, click Delete to remove it.
  Custom Style	In addition to the quick styles provided by the system, you can configure custom styles as needed. You can configure custom styles for both measure fields and dimension fields, but the supported style options differ. Measure fields: You can configure Highlight Text/Background, Icon, Color Scale, and Data Bar. Highlight Text/Background In Rule: Set the rules for highlighting the text of the current measure field. You can use the Based on Field setting to control which data field the rule applies to. By default, it applies to the field selected in the Select Field section. You can also select other fields to configure more complex conditional rules based on their values. In More Settings: Set whether to hide the text of the current field and whether to apply the background color to the entire row. Icon In Rule: Set the icon marking rules for the current measure field. You can use the Based on Field setting to control which data field the rule applies to. By default, it applies to the field selected in the Select Field section. You can also select other fields to configure more complex conditional rules based on their values. In More Settings: Set whether to hide the text of the current field. Color Scale In Rule: Set the display rules for the color scale of the current measure field. In More Settings: Set the shape of the color scale bar and whether to hide the text of the current field. Note You can select and configure the color scale only if Highlight Text/Background, Icon, and Data Bar are not selected. Data Bar In Rule: Set the display rules for the data bar of the current measure field. In More Settings: Set whether to hide the text of the current field and customize the background color of the data bar. Set whether to hide the text of the current field. Dimension fields: You can configure Highlight Text/Background and Icon. Highlight Text/Background In Rule: Set the rules for highlighting the text of the current dimension field. In More Settings, set whether to hide the text of the current field. Icon In Rule: Set the icon marking rules for the current dimension field. In More Settings: Set whether to hide the text of the current field.
  Apply Conditional Style to Summary Data	Select this option to apply the configured conditional styles to summary data.
  Sync Style To	Synchronize the current conditional format to other fields.

• In the Total/Subtotal section, click the switch to enable column totals. When this feature is enabled, all numerical values in a column are summarized.

  

  Configuration item		Description
  Overall Summary (Column Total)	Total Alias	Set the display name for the total data based on your business needs. For example, Total Sales by Region.
  	Calculation Method	Select a uniform aggregation method for the totals of all fields. If a field is a calculated field, the aggregation method that you select here does not take effect. The original aggregation method is used instead.
  	Style Configuration	Set the text style for the total data. You can use the system default or customize the font color, background color, and font weight.
  	Pin to Bottom	Set whether to pin the column total data to the bottom.

  Note

  For frequently asked questions about total configuration, see FAQ about Totals.

• In the Filter section:

  • After you enable Field Content Filter Display, you can click the Selected Fields drop-down list on the fact table to filter or sort fields in preview mode.

  • After you enable Quick Filter, you can click the Filter drop-down list on the fact table to filter fields in preview mode.

    

• In the Auxiliary Display section, you can set the Scrollbar Display Mode and Scrollbar Thickness.

  • If you set the display mode to Show on Mouse Hover, the scrollbar appears only when you move your mouse pointer over the table.

  • If you want the scrollbar to always be visible, select Always Show.

  • If you want to make the scrollbar more noticeable, you can adjust its thickness.

    

Configure Chart Analysis

Display Secondary Indicators

After you configure measures, you can enable and configure secondary indicators.

After you select Display Secondary Indicator, the comparison indicator drop-down list lets you select all measures.

• Comparison Date Selection: You must select a date field from the dataset.

  Note

  • If the chart contains date fields, the secondary indicator comparison date can only be the date field with the smallest granularity in the chart.

  • If the chart does not contain date fields, the secondary indicator comparison date can be any date field in the dataset.

• Comparison Method: Based on the granularity of the date field, the corresponding comparison content is displayed, with a maximum of four comparison fields. For example, if Comparison Date is set to day granularity, Comparison Content Selection displays Day on Day, Week on Week, Month on Month, and Year on Year. If Comparison Date is set to month granularity, Comparison Content Selection displays Month on Month and Year on Year.

• Display Content: Difference Percentage is selected by default. You can select Difference or Original Value.

  • Difference: The data display format is consistent with the indicator's display format.

    When you select Difference, you can select Calculate pt for percentage indicator difference. This option is not selected by default. If you select this option and the indicator is in a percentage format, the display content is the calculated difference value. For example, 5.2% - 4.1% = 1.1 pt.

  • Difference Percentage: The display format retains two decimal places.

• Same Period Comparison (takes effect only when the query filter is bound to day granularity): If you configure filter conditions, the same-period comparison also includes the filter. For example, if you configure a filter condition for 2022-01-02, the 2021-01-02 filter condition is also applied to the data from 2021 when calculating the year-on-year comparison. Day-on-day comparison automatically supports same-period comparison. Dynamic period-over-period comparison does not support same-period comparison.

• When Previous Period Data is Negative: You can select the formula for calculating the difference percentage. The formula that you select takes effect only for data items where the previous period's data is less than 0. You can set it to:

  • (Current Period Data / Previous Period Data - 1) × 100%: Default value

  • 1 - (Current Period Data / Previous Period Data) × 100%

  • Display As -

• Sync Comparison Indicator: You can synchronize the current indicator's configuration to multiple indicators.

• Display Position: The default is Right of Indicator. You can also select Independent Right or Below Indicator.

  • Right of Indicator: Adds a new column to the right of the current indicator. The indicator column name is Year on Year, Day on Day, Month on Month, or Week on Week, based on the selected comparison method.

    If indicators are in columns, secondary indicators are displayed sequentially to the right of the indicators.

    

  • Independent Right: The last column dimension is split to display Day on Day, Week on Week, Month on Month, or Year on Year, based on the selected comparison method. This exists independently to the right of the current indicator.

    

  • Below Indicator: Adds multiple rows of comparison indicators below the current indicator. The alignment matches the indicator's alignment: text is left-aligned, and values are right-aligned.

    It shares a cell row with the current indicator, adding a row below the current indicator's header for Day on Day, Week on Week, Month on Month, or Year on Year, based on the selected comparison method.

    

• Display Style: You can select the display colors for positive and negative numbers in the table. You can choose to follow the Global Semantic Color Settings (the settings in Page Settings > Global Style > Semantic Colors), Positive numbers in red, negative numbers in green, or Positive numbers in green, negative numbers in red.

FAQ about Totals

Total calculation description

• If the original data is GMV (Sum) and the calculation method for totals and subtotals is set to Automatic, the calculation logic for totals and subtotals is GMV (Sum). If you switch the total to Custom and select Average as the aggregation method, the calculation logic for the total is GMV (Average).

• If the original data is id (Count Distinct) and the calculation method for totals and subtotals is set to Automatic, the calculation logic for totals and subtotals is id (Count Distinct). If you switch the total to Custom and select Count as the aggregation method, the calculation logic for the total is id (Count).

How do I calculate a rate?

1. Define the rate.

   In this example, the rate is calculated based on the following formula: Rate = Measure A / Measure B.

2. Create a calculated field in the dataset and define the expression as: rate=sum(A)/sum(B).

3. In the data panel of the dashboard, select the rate (Sum) measure and select totals and subtotals. The calculation logic defaults to automatic rate (Sum), so the calculation logic for totals and subtotals is sum(A)/sum(B).

   Note

   If the expression that you write in the dataset is rate=A/B and you directly select automatic calculation logic for totals and subtotals as A/B (Sum), the rate may exceed 100%. In this case, the calculation result is incorrect.