Column line combination chart

1. Overview

1.1 Application scenarios

The column chart and the line chart are a common combination. After the chart is combined, you can use the column chart to compare the size of the data, and you can view the trend of the data through the line chart.

This article compares the example of the combination of column chart and line chart, and introduces how to achieve the combined effect of these two charts. The contrast column chart is a variant of the column chart.

1.2 Data requirements

1.3 Chart features

Advantages: Two dimensions of data can be displayed centrally, which gives decision makers more information insights. And it can save report layout space, allowing more layout to display other data.

Disadvantages: When multi-dimensional data is concentrated in the combination chart, the chart will be too crowded. At the same time, the increase of indicators will interfere with the decision-maker's to grasp the data and convey the main information.

2. Example

2.1  Data preparation

2.1.1 Prepare Excel file

Create a new Excel file and create four columns: "Date", "Cost type", "Amount of money" and "Included Labor Cost", where labor cost is part of the corresponding amount, as shown in the following figure:

Click to download the Excel file: 

272.Data of combination chart.xlsx

2.1.2 Add Execl Dataset

Click "Data Preparation" in FineBI to create a business package, then click "Add Table" and select "Excel Dataset". Import the above Excel file and name it as "Data of combination chart", as shown in the figure below:

2.1.3 Add self-service dataset

1) In order to achieve the effect of comparing the column chart, you need to process the original data in Excel. Click "Add Table" and select "Self-service Dataset", as shown in the figure below:

2) Name the self-service dataset "Self-service dataset of combination chart", check all fields, click the add button on the left, and select "New column", as shown in the figure below:

3) Change the name of the newly added column to "New amount of money" and fill in the calculation formula: IF(Cost type = "Operating Costs",-1*Amount of money, Amount of money). Click OK to save, as shown in the figure below:

Note: The formula multiplying the Operating costs by -1 is to achieve the effect of Contrast column chart.

4) Click "Save" and exit the self-service dataset interface, as shown in the figure below:

5) Select the newly created self-service dataset and update the data, as shown in the figure below:

2.2 Create components

1) Create a new dashboard, as shown in the figure below:

2) Click to add component, as shown in the figure below:

Select the newly created self-service dataset and click OK, as shown in the figure below:

2.3 Design components

1) Drag the "Date" field under the dimension into the horizontal axis, and drag the "New amount of money" and "Included labor cost" fields under the indicator into the vertical axis.

And rename the "New amount of money" field to "R&D/Operating Costs", and select "Custom Chart" for the chart type.

Select "Included labor cost" as a line chart display, and set "R&D/Operating Costs" as a column chart display. The specific effect is shown in the following figure:

2) Drag the "TargetName" field and the "Included labor cost" field to the "color" field and "tag" field of the line chart respectively, set the color to "Yellow", and set the tag unit to "Thousand", as shown in the figure below:

3) Drag the "Cost type" field and the "Amount of money" field to the "color" field and the "tag" field of the column chart, respectively.

Set the color of "Operating Costs" to red and the color of "R&D Operating" to green, as shown in the following figure:

Note: Since the amount of money is a positive value, our tag takes the "Amount of money" field.

4) Set the title, enter "Column line combination chart". Set the legend, and select the position to display on the right, as shown in the following figure:

2.4 Set the dimension field

1) Set the " Included labor cost " field, select the right-value axis, set the axis tag, select 12 for the font size, and select blue for the font.

Set the axis scale and define the minimum value as -4000  and the maximum value is 500+MAX(Included labor chart(Sum)). And finally set the axis title to boldface and font size to 13. As shown below:

2) Set the " R&D/Operating Costs" field, select the left-value axis, set the axis tag and select 12 for the font size, and select blue for the font.

Set the axis scale and define the minimum value as MIN(R&D/Operating Costs(Sum)), and the maximum value is MAX(R&D/Operating Costs(Sum)), And finally set the axis title to boldface and font size to 13. As shown below:

4. Dashboard View

For complete dashboard, please refer to : Combination Chart.