Calculate proportion in group-indicator

1. Overview

1.1 Version

1.2 Application scenario

Need to group by dimension A field, classify the group by dimension B field, and calculate the indicator's proportion in the group. For example, group by "year" and calculate the proportion of "Volume" of each commodity category in the current year. The effect is shown in the following figure:

There are other documents about calculating the proportion in the group, as shown in the table below:

1.3 Implementation ideas

According to different implementation methods, there are two schemes of using fast calculation or function to realize the proportion in the group. Fast calculation is more convenient. If the jar package version after 2020-09-02 is not updated, the function can be used to calculate the proportion in the group.

1.3.1 Scheme I. Use Quick calculation to get the proportion in the group

In the process of making components, we click the "" drop-down button on the right side of the target field to perform "quick calculation" →"proportion in the group" to obtain the indicator data of the group proportion. It has faster implementation. As shown in the figure below:

1.3.2  Scheme II: Use function to calculate the proportion in the group

The same effect can also be achieved by addingformulas, and the proportion indicators in the group are calculated, as shown in the figure below:

2. Scheme 1: Use quico calculation to get the proportion in the group

Sample data:

193.SaleOfYear&Month.xls

The "proportion in the group" calculation needs to be realized on the component editing page. First upload the data and add components to enter the page.

2.1 Upload data

1) Open "Data Preparation" → "Self-Service Dataset" → "Add Table" to add in excel dataset, as shown in the following figure:

2) After adding the "Sale of Year & Month" dataset, since the "Volume" needs to be analyzed by the "year" dimension during component editing, the field type needs to be modified to "text", as shown in the following figure:

Note: when the field type is "value", it cannot be analyzed as a dimension, but only be analyzed as an indicator.

3) Change the "month" to the "text" field, then fill in the form name and click "OK" to complete the data uploading. As shown in the figure below:

2.2 Create components

After uploading the data, FineBI will update automatically. Click "create component", fill in the dashboard information of the component, and click "OK" to complete the component creation. As shown in the figure below:

2.3 Set the component

The expected effect is to view "volume" by "name of goods" and group by "year". Since pie chart requires a dimension and an indicator, you can first use "name of goods" and "volume" to turn the chart into a pie chart, and then drag "year" to group "name of goods" for detailed pie chart making.

1) First drag "name of goods" and "volume" into the dimension indicator column, as shown in the figure below:

2) To realize the proportion of group of "volume", click "" →  "Quick Calculation" → "POroportion of the group" to display the calculation results of group proportion. Then click "pie chart" to switch to pie chart component editing. As shown in the figure below:

3) After the pie chart effect is completed, drag the "Year" into the horizontal axis, and the chart preview interface realizes proportion of the group effect, that is, the analysis results of the volume proportion of eachgood in each year are obtained. As shown in the figure below:

2.4 Component beautification

Components can also be beautified according to user needs to show clearer component analysis results. Component beautification can refer to the file: graphic properties.

1) Add a ring chart label. Click "Volume (sum - proportion in group)" → "Copy" to copy the indicator, and then drag it into the tab. As shown in the figure below:

2) Display the label outside. Click the field "Volume(sum - proportion in group)" in the label to set the label position "Outer", as shown in the following figure:

3) Change the label to percent. Click the field "" →"numeric format" in the label, set it to "percentage", and click" OK "to complete the operation. As shown in the figure below:

4) Change the ring color. Click "name of goods" in the color field to modify the color scheme. After completing component production and beautification, click "enter the dashboard", as shown in the following figure:

3. Scheme 2: use function to calculate the proportion in the group

The data used in the example is the same as that in scheme I. The data can be downloaded in Section 2.

The group proportion calculation needs to be realized on the component editing page. First upload the data and add components to enter the page. The steps are the same as above, refer to sections 2.1 and 2.2 of this document.

3.1 Add calculation indicator

After entering the component editing page, add the proportion indicator in the group. As shown in the figure below:

Add formula:SUM_AGG(Volume)/TOTAL(SUM_AGG(Volume),1,"sum") , use the Volume summarized in groups to obtain the proportion of the total volume, name the calculation index as "proportion in group", and click "OK". As shown in the figure below:

Note: the field in the formula needs to be selected by clicking the field column on the left. Manual input is invalid.

Formula Description:

3.2 Make the pie chart

Drag the fields from the area to be analyzed into the dimension indicator column of the analysis area, and click "pie chart" to make the pie chart. As shown in the figure below:

The displayed pie chart needs to be displayed by "Year", drag "Year" into the dimension field, as shown in the following figure:

3.3 Component beautification

The steps are the same as above, and refer to Section 2.4 of this document.

4. View the effects

4.1 PC terminal

See section 1.2 of this document for details of component effects.

For the completed dashboard, see the file: "pie chart of proportion in the group".

4.2 Mobile terminal

The effect of mobile terminal is shown in the figure below: