Marking the Maximum/Minimum Value in a Table

Version

Expected Effect

You want to mark the maximum and minimum values of an indicator and set the values to be displayed in different colors in a table.

For example, the maximum and minimum values of the Sales Volume field (corresponding to the District field) are marked in red and green respectively, as shown in the following figure.

Implementation Method

1. You can add a summary column named District Total Sales to calculate the total sales for each district and then continue to add two summary columns named District Maximum Total Sales and District Minimum Total Sales to calculate the maximum and minimum values accordingly.

2. You can add a formula column named flag and set it as below:

A row where the value in the District Total Sales field equals that in the District Maximum Total Sales field is flagged as 1.

A row where the value in the District Total Sales field equals that in the District Minimum Total Sales field is flagged as -1. Otherwise, the row is flagged as 0.

3. You can aggregate Sales Volume by District:

If a row is flagged as 1, the value in the District field must be flagged greater than 1.

If a row is flagged as -1, the value in the District field must be flagged less than -1.

4. You can set the value in the Sales Volume field (corresponding to the District field) to be displayed in red if that value is flagged greater than 1 and in green if less than -1.

Data Preparation

1. Log in to FineBI, click My Analysis, select a folder, and click New Subject.

2. Click Local Excel and Upload Data.

You can download the sample data: Stores Sales Data Statistics.xlsx

3. Click OK after the data is uploaded.

4. Click Summary Column in the top toolbar and name the to-be-added column as District Total Sales in the pop-up dialog box.

5. Click Select Group Field, check District, and click OK. Set Summary Field to Sales Volume, Summary Method to Sum, and click OK to calculate the total sales for each district, as shown in the following figure.

6. Click Summary Column and name the to-be-added column as District Maximum Total Sales in the pop-up dialog box.

7. Set Summary Field to District Total Sales, Summary Method to Maximum, and click OK to calculate the maximum value of the total sales for each district, as shown in the following figure.

8. Click Summary Column and name the to-be-added column as District Minimum Total Sales in the pop-up dialog box.

9. Set Summary Field to District Total Sales, Summary Method to Minimum, and click OK to calculate the minimum value of the total sales for each district, as shown in the following figure.

10. Click Formula Column and name the to-be-added column as flag in the pop-up dialog box.

11. Enter the formula in the input box: IF(District Total Sales=District Maximum Total Sales,1,IF(District Total Sales=District Minimum Total Sales,-1,0)).

The following table describes the formula.

12. Click OK to add the flag column, as shown in the following figure.

13. Click Save and Update and Exit and Preview to complete the data creation, as shown in the following figure.

Component Creation

1. Click the Component tab in the lower left corner, as shown in the following figure.

2. Select Group Table in Chart Type.

3. Drag the District field in the to-be-analyzed area into Dimension and Sales Volume into Indicator, as shown in the following figure.

Text Color Setting

1. Drag the flag field in the to-be-analyzed area into Color under Table Property > Indicator.

2. Click Color and click the Add Condition button in the pop-up settings box.

3. Set the value in the Sales Volume field to be displayed in green if that value is flagged less than -1. Set the value in the Sales Volume field to be displayed in red if that value is flagged greater than 1, as shown in the following figure.

Note:

1. If you do not check View All data, the flagging effect may not be displayed on the Dashboard editing page, and you need to go to the preview page to view the effect.

2. Summary row data flagged in red will not be displayed if you uncheck Total Summary Row under Component Style > Summary Row.

Dashboard Creation

1. Click the Add Dashboard button at the bottom of the analysis subject editing page.

2. Drag the component into the dashboard on the Dashboard editing page.

3. Select Edit Title from the drop-down list, set Font Style to Custom, set the title to Marking the Maximum/Minimum Value in a Table, and click OK.

Effect Display

1. On PC

2. On Mobile Terminals