Marking the Maximum/Minimum Value in a Table

Version

Expected Effect

You want to mark the maximum and minimum values of an indicator 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 Total Sales of Each District to calculate the total sales for each district and then continue to add two summary columns named Maximum Total Sales of Each District and Minimum Total Sales of Each District 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 Total Sales of Each District field equals that in the Maximum Total Sales of Each District field is marked as 1.

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

3. You can aggregate Sales Volume by District:

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

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

4. You can mark a value in the Sales Volume field (corresponding to the District field) in red if that value is marked as greater than 1 and in green if that value is marked as 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: Store 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 Total Sales of Each District in the pop-up dialog box.

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

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

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

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

9. Set Summary Field to Total Sales of Each District, Summary Method to Minimum, and click OK to calculate the minimum total sales of 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: IF(Total Sales of Each District=Maximum Total Sales of Each District,1,IF(Total Sales of Each District=Minimum Total Sales of Each District,-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 button in the lower left corner, as shown in the following figure.

2. Select Group Table in Chart Type.

3. Drag the District field from 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 from the to-be-analyzed area into Color under Table Property > Indicator.

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

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

Note:

1. If you do not select View All data, the flagging effect may not be displayed on the dashboard editing page. In this case, you need to go to the preview page to view the effect.

2. Data marked in red in the summary row will not be displayed if you deselect 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. PC

2. Mobile Terminal