Calculate the proportion in the horizontal group

1. Overview

Users sometimes need to calculate the horizontal proportion.

1.1 Expected effect

When using the "crosstab" to make a chart, it is necessary to calculate the "Sales of living halls as a percentage of total sales" and "Sales of fashion halls as a percentage of total sales".

For example, in 2017-05, the proportion of living halls: 66.25%=3614113/(3414113+1840913)*100%;

the proportion of fashion halls: 33.75%=1840913/(3414113+1840913)*100%.

1.2 Implementation ideas

Copy the "Sales" indicator and perform detail filtering. The filter condition is that the store style belongs to "fashion hall". Add a calculation indicator: fashion hall monthly sales/monthly total sales;

In the same way, calculate the proportion of monthly sales of the living hall.

2. Operation steps

Sample data: "Retail industry> Data statistics of store sales".

2.1 Create a dashboard

Use "Data statistics of store sales" to create a dashboard and enter the component editing interface, as shown in the following figure:

2.2 Copy the indicator and filter

Copy the "Sales" indicator of the area to be analyzed, and name the copied indicators "Living hall sales" and "Fashion hall sales", as shown in the following figure:

Perform "Detail Filtering" on "Living hall sales", set the filter condition to "Shop Style in Living hall", and click "OK", as shown in the figure below:

In the same way, the same filtering is applied to "Fashion hall sales".

2.3 Calculate the proportion

1) Add a calculation indicator and name it "Sales of living halls as a percentage of total sales", and enter the formula: SUM_AGG (Living hall sales)/SUM_AGG (Sales). Click "OK", as shown in the figure below:

2) In the same way, calculate the "Sales of fashion halls as a percentage of total sales", as shown in the figure below:

3) Select the chart type as "Group table", drag these indicators into the analysis area, and set the grouping basis of "Sales date" as "Year Month", as shown in the figure below:

4) Change the numerical format of "Sales of living halls as a percentage of total sales" and "Sales of fashion halls as a percentage of total sales" as a percentage.

2.4 Effect view

See section 1.1 of this article for details.