Calculation After Distinct Count and Filter of Data

Expected Effect

This document takes the Store Dimension table as an example to calculate the number of managed stores in each district, as shown in the following figure.

Implementation Method

1. You can convert the dimension field Shop Number to an indicator field to obtain the distinct count of the number of stores.

2. You can add a filter condition to the indicator field Shop Number to obtain the number of managed stores. The filter condition is that the store nature is Managed.

3. However, since each district has a managed store that belongs to the headquarters, you need to add a calculation indicator and enter Shop Number+1 to obtain the number of managed stores that include the managed stores belonging to the headquarters.

Note:

You need to convert the dimension field to an indicator field before adding the field to the formula of the calculation indicator. Even though no aggregate function is used in the formula, the calculation indicator field is still displayed as an aggregate function. Converting a dimension field to an indicator field corresponds to obtaining the distinct count based on the dimension field in the internal calculation logic. Therefore, you cannot use the aggregate function COUNT_AGG in non-aggregate calculations. For details, see section "No Mix Use of Aggregation and Non-aggregation Parameters" in Checking the Causes of Invalid Formulas.

4. Finally, you can add a group table to display the analysis result.

Note:

The distinct count function is not supported in detail tables.

Sample data: Store Dimension.xlsx

Goal: to obtain the number of managed stores in different districts

Dashboard Creation

Download the sample data Store Dimension.xlsx, create an analysis subject, and choose Local Excel > Upload Data, and upload the sample data, as shown in the following figure.

Component Adding

Click the Component icon in the lower left corner to add a component, as shown in the following figure.

Converting the Dimension Field to an Indicator Field

Click the drop-down icon of Shop Number and select Convert to Indicator to obtain the distinct count of the number of stores, as shown in the following figure.

Detail Filter

The number of managed stores needs to be calculated. Click the drop-down icon of the indicator field Shop Number, select Indicator Condition, and add a filter condition for Shop Number. The filter condition is that the store nature is Managed, as shown in the following figure.

Calculation Indicator Adding

Since each district has a managed store that belongs to the headquarters, you need to add one to the result of Shop Number obtained by the filter condition during the calculation of the number of stores in each district.

Add a calculation indicator, name the indicator Number of Managed Store, enter Shop Number+1, and click OK, as shown in the following figure.

Note:

You need to convert the dimension field to an indicator field before adding the field to the formula of the calculation indicator. Even though no aggregate function is used in the formula, the calculation indicator field is still displayed as an aggregate function. Converting a dimension field to an indicator field corresponds to obtaining the distinct count based on the dimension field in the internal calculation logic. Therefore, you cannot use the aggregate function COUNT_AGG in non-aggregate calculations. For details, see section "No Mix Use of Aggregation and Non-aggregation Parameters" in Checking the Causes of Invalid Formulas.

Group Table Adding

Drag the fields from the to-be-analyzed area into the Dimension and Indicator bars, respectively, and deselect Total Summary Row in Component Style to obtain the number of managed stores in different districts. Click the Dashboard icon, and complete the operation, as shown in the following figure.

PC

For details, see section "Expected Effect."

Mobile Terminal

The following figure shows the effect on mobile terminals.