Overview
Background
We can easily see whether each state has hit the business goal, but such general summary would cause us to miss many details.
For example, the reason behind the above-goal achievement may be the impressive performance of certain well-sold products.
To avoid this scenario and achieve overall above-goal business performance, we need to go deeper into the business details and seek for improvement.
Preview
Calculate the proportion of products reaching the goal in each state. It is clear that Texas's success in hitting the goal is because most products’ sales performance in the state have overshot the target.
Click the state column on the left to view detailed information in the right chart.
Procedures
Adding Data
Example data: Business Goal Table.xlsx
Create a subject and upload the sample data.
Creating Components
To know the proportion of profitable products to the total number of products in each state, set the State field as a dimension and the Proportion field as an indicator.
The formula is proportion = number of product hitting the goal / total product number.
1. Calculating the Number of Products Hitting the Goal
a. Add a calculation indicator to calculate the gap between the current profit and the profit goal.
Formula | Description | Help Document |
SUM-AGG (Profit) | Calculate the profit sum | SUM-AGG |
DEF(SUM-AGG(Profit), [State, Product]) | Calculate the profit sum of different products in different states. | DEF |
SUM-AGG (Profit Goal) | Calculate the sum of profit goals. | SUM_AGG |
DEF(SUM_AGG(Profit Goal), [State, Product]) | Calculate the sum of profit goals of different products in different states. | DEF
|
DEF(SUM_AGG(Profit), [State, Product])-DEF(SUM_AGG(Profit Goal), [State, Product]) | Calculate the gap between profit and goal of different products in each states. | / |
b. Add a calculation indicator to calculate the number of products hitting the target in each state.
Formula | Description | Help Document |
COUNTD_AGG(Product) | Distinct count the product number. | COUNTD_AGG |
DEF(COUNTD_AGG(Product), [State], Gap Between Profit and Goal>0) | Distinct count the number of products with above-goal performance in each state. | DEF
|
2. Calculating the Total Product Number in Each State
Delete the filter condition [Gap Between Profit and Goal>0] in the formula set in the previous step, then you can calculate the total product number.
3. Calculating the Proportion
You have already calculated the number of products with above-goal sales performance and the total product number in each state. Then you can get the proportion through the formula: the number of products hitting the goal / the total product number.
Add a calculation field and set it like this:
4. Creating Components
Set the chart type as Partitioned Column Chart, drag the fields to the horizontal axis and vertical axis respectively, and drag the Proportion field to Label.
Now the core component is created, and it is clear that 75% of Products in Texas has hit the profit goal.
Effect Display
For details, see section “Preview”.
