Filtering Level Introduction

Since calculations are sequential, filtering also has levels. The calculation logic and effect vary according to the calculation position where you add filtering conditions.

You can make great progress in the data analysis by learning about the filtering level.

If you change the filtering order for the same calculation, you will get different results.

For example, you need to perform the following two steps for a group of data. First, filter data with sales equal to or greater than 100. Then calculate sales for each region. The final result displayed is the branches with sales equal to or greater than 100 and the total sales for each region.

Now, change the order of filtering and calculation as follows. First, calculate the sales for each region. Then, filter the data with sales equal to or greater than 100. The final result displayed is the regions with total sales equal to or greater than 100 and their sales.

There are four filtering levels in FineBI. The final calculation result varies according to the level at which filtering is perfomed. The following specifically describes the filtering levels in FineBI.

Calculations are sequential. The following table lists the logical calculation order in FineBI. In the practical analysis, all the following four calculations may not necessarily be used.

Calculation Order	Filtering Performed After the Calculation
Edit the data table.	Detail filtering: Perform a filtering on the processed data table.
Add a DEF calculation field to the component.	New column filtering: After a new column is output after the DEF function is used, perform filtering on the data table.
Drag the field into the component to complete the aggregation calculation.	Aggregation calculation filtering: Perform filtering on the result of the aggregation calculation.
Use the quick calculation function to recalculate the aggregation value (secondary calculation).	Quick calculation filtering: Perform filtering on the result of the quick calculation.

The following streamlined diagram visually shows that the more to the left the filtering is, the more calculations it affects. For example:

If detail filtering is performed, the calculation result of the DEF calculation field, component aggregation, and quick calculation will all be affected.

If aggregation calculation filtering is performed, the result value of aggregation calculation will not be affected.

Assume that you are the general manager of a shopping mall. You want to learn about the products that repeat customers prefer in 2021 to determine which products can attract and retain customers.

You can download the example data: Office Supplies Data.xlsx.

Create an analysis subject, upload the sample data, and enter the Component tag page.

Detail Filter

Drag Contract Time into Filter and filter the data for 2021, as shown in the following figure.

New Column Filtering

1. Use the DEF function to calculate the quantity of orders per customer in 2021, as shown in the following figure.

2. Filter the order data where the quantity of purchased orders is greater than or equal to 2. Such data is the order data of repeat customers.

Component Calculation

1. Create a calculation field Customer Quantity, as shown in the following figure.

2. Create a group table and drag the Product Name, Sales, and Customer Quantity fields into the table. View the quantities of repeat customers for each product and the corresponding sales.

Aggregation Calculation Filtering

There are two reasons for a high repurchase rate among users: low product prices and excellent product quality that is attractive to users.

To find attractive products, you need to exclude the price reduction factor, that is, filter out products whose profit margins are too low.

1. Create a calculation field Profit Margin, as shown in the following figure.

2. Filter products with a profit margin greater than 0.1, as shown in the following figure.

The following figure shows the post-filtering effect. According to the result, aggregation filtering does not affect the value results of aggregation calculations, for example: the values of Sales and Customer Quantity for Office Chair before and after the aggregation filtering are the same.

Quick Calculation Filtering

Rank the quantities of customers and filter the top 5 products.

1. Use the quick calculation to rank Customer Quantity, as shown in the following figure.

2. Filter the top five products, as shown in the following figure.

Effect Display

The final result displayed is the top five products by repurchase times (with the impact of low-price promotions excluded) in 2021.

The following figure shows the calculations and filtering you did throughout the entire process.