I. Overview
1) A proportion is often used to indicate the ratio of a value to the total, for instance:
The proportion of a salesperson’s sales volume in the national total.
The proportion of a salesperson’s sales volume in the regional total.
2) This article introduces how to calculate a proportion through hierarchical coordinates.
II. Steps
1. Create a new dataset [ds1]
1) Create a new DB Query.
2) Input the following SQL function:
SELECT Region, Salesperson, SUM(Sales_Volume) AS SalesFROM Sales_VolumeGROUP BY Region, Salesperson
2. Design the report body
1) Type headers
2) Drag data columns in [ds1] into the cell range B3:D3.
3) Preview.
3. Calculate the proportion
1) Input the following formula in E3: D3/SUM(D3[!0]{B3=$B3}).
Formula segment | Meaning |
SUM(D3[!0]{B3=$B3}) | D3[!0]{B3=$B3} fetches all sales volumes in a region. The region is where a salesperson is located. SUM(D3[!0]{B3=$B3}) calculates the total sales volume in the region. |
D3/SUM(D3[!0]{B3=$B3}) | D3 is the salesperson’s sales volume. D3/SUM(D3[!0]{B3=$B3}) is the proportion of a salesperson’s sales volume in the regional total. |
2) Input the following formula in F3: D3/SUM(D3[!0]).
Formula segment | Meaning |
SUM(D3[!0]) | D3[!0] fetches all sales volumes nationwide. SUM(D3[!0]) calculates the total sales volume nationwide. |
D3/SUM(D3[!0]) | D3 is the salesperson’s sales volume. D3/SUM(D3[!0]) is the proportion of the salesperson’s sales volume in the national total. |
3) Set the style of text in E3 and D3 to [Percentage], in the form of #0.00%, that is, with two decimal places reserved.
4. Preview
