[Direct] Control calculation granularity function

1.1 Version

1.2 Application scenarios

Aggregate function, according to the switch of the "dimension" as the user analyzes, the calculation result of which will be automatically adjusted dynamically following the "dimension".

But when the analysis area needs to have multiple dimensions, and at the same time it is necessary to calculate the aggregate data based on a certain dimension as the grouping basis, a fixed-granularity calculation function needs to be used.

Include、Exclude、Fixed functions can better control the level of granularity to be calculated. These functions can be executed at a higher granularity level (include), a lower granularity level (exclude), or a completely independent level (fixed).

1.3 Function introduction

• FIXED function: Perform summary operations with specified dimensions, as shown in the following figure:

• EXCLUDE function: Exclude a specified dimension to perform summary operations, which is very useful for the "percentage of total" or "difference from the overall average" scheme, as shown in the following figure:

• INCLUDE function: Perform summary calculation based on the dimension and include the dimension dragged into the analysis area.

This article describes the types of expressions that can be used in FineBI to control calculation granularity, when to use these expressions and how to format them.

Note 1: Functions and aggregate functions are not supported for calculation of addition, subtraction, multiplication, and division.

Note 2: Conversion of calculated indicators into geographic roles is not supported.

The FIXED function expression uses the specified dimension to calculate the value, and does not refer to the dimension in the analysis area.

2.1 Overview

2.2 Notice

• Function calculation results can be converted into dimension fields.

• The function does not support detail filtering.

• It supports mutual operations with constants and indicator fields.

2.3 Example

Sample data: FRDemo_Sales_Volume.xls

For example, to find the proportion of indicators in the group under any grouping, to analyze data, the business needs to see the three dimensions as "Salesperson, Product_types, and Product" in a table. There are one indicator as "Sales_Volume". It is necessary to analyze the percentage of sales of specific salesperson in all salespersons.

Use the sample data to create a dashboard, add calculation indicators, name and enter the formula:FIXED (Salesperson, Product_types, SUM_AGG (Sales_Volume))/FIXED (Salesperson, SUM_AGG (Sales_Volume)), as shown in the following figure:

Formula description:

Drag the fields into the analysis area to display the three dimensions of "Salesperson, Product_types, and Product" in one table, and calculate the proportions with "Salesperson" and "Product_types" as fixed dimensions, as shown in the following figure:

3.1 Overview

3.2 Notice

• Function calculation results cannot be converted to dimension fields

• The function does not support detail filtering.

• It supports mutual operations with constants and indicator fields.

3.3 Example

The EXCLUDE function expression is used to omit the specified dimension from the dashboard.

For example, still taking the question in section 2.3 as an example, to analyze a piece of data, the business needs to see the three dimensions as "Salesperson, Product_types, and Product" in a table. There are one indicator as "Sales_Volume". It is necessary to analyze the percentage of sales of specific salesperson in all salespersons

Contrary to the operation in section 2.3, calculation can be done by eliminating unnecessary dimensions.

Use the sample data to create a dashboard, add calculation indicators, name and enter the formula:EXCLUDE (Product, SUM_AGG (Sales_Volume))/EXCLUDE (Product, Product_types, SUM_AGG (Sales_Volume)), as shown in the following figure:

Formula description:

4.1 Overview

4.2 Notice

• Function calculation results cannot be converted to dimension fields

• The function does not support detail filtering.

• It supports mutual operations with constants and indicator fields.

4.3 Example

For example, there are four dimensions of 'Region, Salesperson, Product_types, and Product", and one indicator of "Sales_Volume". To analyze the "average sales" of a salesperson, you can use the function to first calculate the average sales of this dimension of the salesperson, and then through sragging to the different dimensions and including indicators of the analysis area, so as to realize the analysis specifically for the average sales of the salesperson.

When adding or removing dimensions in the dashboard, the calculation granularity based on INCLUDE expressions will also change. Therefore, the average value of the average sales of salespersons in the region can be seen by dragging the region to which it belongs.

Add the field "store average sales", enter the formula:INCLUDE (Salesperson, AVG_AGG (Sales_Volume)), as shown in the figure below: