Calculation Type Selecting

This document introduces the calculation types you can use in FineBI.

You can use the following four calculation types when adding a calculation indicator in FineBI:

1. Aggregation calculation

2. Detail level calculation (data source row level calculation)

3. Functions that control the calculation granularity

4. Component quick calculation

You can download the sample data: Contract Fact.xlsx.

Generally, to achieve an aggregation calculation, you need to use aggregate functions to summarize the values of a set of data and then use the summarized values to perform a further calculation. For details, see Overview of Aggregate Functions.

For example, you want to process the data in the contract below.

If you want to have a column to display the count of contract IDs corresponding to each contract type, you can go to the Component editing page, click the  icon, select Add Calculation Indicator from the drop-down list, input the formula: COUNT_AGG(Contract ID), and click OK, as shown in the following figure.

The following figure shows the calculated results.

Note:

The colors help to display the detail level at which the calculations are performed.

The following figure shows the table effect on the Component editing page.

Row level calculations of data sources can be achieved by adding columns to self-service datasets and adding calculation indicators on the Component editing page.

For details of various functions, see Function Calculation Format.

Dashboard Calculation

You are not advised to perform a detail level calculation on the Component editing page of a dashboard.

The application scenario of a detail-level calculation: For example, each order record contains data of the total sales price and the unit one, and you want to use those data to calculate the total purchase quantity. In this case, you can add a calculation indicator, input the formula: Total Sales Price/Unit Price, and add up data of the purchase quantity for each order to get data of the total purchase quantity when summarizing.

You can use the sample table Contract Fact. You can go to the Component editing page, click the  icon, select Add Calculation Indicator from the drop-down list, name the to-be-added field as Unit Price, input the formula: Contract Amount/Purchase Quantity, and click OK, as shown in the following figure.

The following figure shows the calculated results. The calculation is performed on the row level of the data source, thus rows are set to be displayed in various colors for better differentiation.

The results are obtained through division performed on the detailed data.

Self-Service Dataset Calculation

For details, see Basic Functions of the Added Column.

The results calculated through aggregate functions can be automatically changed according to fields in Dimension.

However, functions that can control the calculation granularity help when multiple dimensions exist in the analysis area, and at the same time you need to calculate the summary data grouped by certain dimensions.

Functions of DEF, DEF_ADD, and DEF_SUB can better control the granularity. These functions can be performed at higher granularity levels (inclusive), lower granularity levels (exclusive), or completely independent levels (fixed).

For example, you want data on the quantity of each product purchased by members on different dates. The dimension field of Date is in the analysis area.

You can group the table data by the two dimension fields: Product and Date, filter the data in the Membership field based on the grouped data, and perform a summary calculation on the Purchase Quantity field data.

You can go to the Component editing page, click the  icon, select Add Calculation Indicator from the drop-down list, name the to-be-added field as Purchase Quantity of Product by Member, input the formula: DEF_ADD(SUM_AGG(Purchase Quantity),[Product],[Membership='Yes']), and click OK, as shown in the following figure.

For details, see DEF_ADD (Analysis Area Dimension and Specified Dimension). 

Quick Calculation allows you to perform calculations or convert value display formats on data in the created chart/table component.

For details, see Quick Calculation - Percentage, Quick Calculation - Ranking, Quick Calculation - All Values, Quick Calculation - Cumulative Value, YoY/MoM Growth (Quick Calculation), Summary Functions for Tables, and Summary Functions for Charts.