Overview
Preview
This document introduces how to calculate the horizontal proportion of cross tables.
You may need to calculate the sales proportion of each product category in each province. For example, in Shanghai, the sales of fresh food accounts for 7.06% of the total.
Implementation Method
Calculate the total sales of each province and the provincial sales of each product category through the DEF function.
The formula is as follows: Proportion = Provincial sales of each product category / Provincial sales.
Procedures
Sample data: Group Sales Table.xlsx
Adding Data
Create an analysis subject and upload the sample data Group Sales Table.
Calculating the Horizontal Proportion
1. Go to the component editing page and select the chart type as Cross Table. Drag the Province field into Row Dimension and the Product Category field into Column Dimension.
2. Calculate the total sales of each province. Add a calculation indicator and enter the formula shown in the following figure.
| Formula | Explanation | Reference |
|---|---|---|
SUM_AGG(Sales) | Sums up the total sales amount. | |
DEF(SUM_AGG(Sales Amount),[Province]) | Groups data by province and sums up the sales amount of each group to calculate the total sales of each province. |
3. Calculate the provincial sales amount of each product category. Add a calculation indicator and enter the formula shown in the following figure.
| Formula | Explanation | Reference |
|---|---|---|
SUM_AGG(Sales) | Sums up the total sales amount. | |
DEF(SUM_AGG(Sales Amount),[Province,Product Category]) | Groups data by province and product category and sums up the sales amount of each group to calculate the provincial sales of each product category. |
4. Calculate the proportion. Add a calculation indicator and enter the formula shown in the following figure.
5. Drag the Proportion of the Sales Amount of Each Product Category field into the analysis area and change the Value Format to Percentage.
Demonstration
See section "Preview."
