Locate stores with abnormal gross profit margins

For video tutorials on locating areas/stores with abnormal gross margins, please see: Locating stores with abnormal gross margins

1.1 Version

In the analysis of sales and gross profit margin, we have found that the gross profit margin in August is abnormal, but the gross profit has become lower, indicating where the cost has increased abnormally.

1.2 Expected effect

In this article, we will use FineBI to find out which area and even which store has problems.

1.3 Implementation ideas

1) First use the filter component to filter out the data for August;

2) Quickly locate which area has abnormal gross profit margin in August through the map component;

3) Click on the abnormal area to screen out the stores in that area through linkage, and locate which store has a huge abnormal gross profit margin.

On the basis of analyzing sales and gross profit, new components are created, and the data table used is still the "commodity sales summary table".

2.1 Matching geographic roles

If you want to make a map component, you need to match the field to the geographic role first.

1) In the "Province" field, select the drop-down > Geographic Role > Province/City/Autonomous Region, as shown in the figure below:

2) FineBI can automatically match the user data table with the geographical information that comes with BI, click OK, as shown in the following figure:

3) Follow the same method to match the "City" field, as shown in the figure below:

4) Due to the continuous change of geographic names, or some hierarchical relationship errors, it cannot be matched. For details, please refer to the document: Geographic Role Matching Rules

Ignore the 10 items that do not match, and click OK.

2.2 Set drill directory

1) In the "Province" field, select drop-down > Create a Drill Directory, and name the drill directory "Province, City" as shown in the figure below:

2) In the "City" field, select drop-down > Add to the drill directory > Province, city, as shown in the figure below:

2.3 Create an are map

1) Drag the province, city (longitude) and province, city (latitude) into the horizontal and vertical axis respectively, and select the chart type as the regional map, as shown in the figure below:

2) Drag the drill directory "province, city" into fine-grained, so that the map can realize the drill function, as shown in the following figure:

3) Create the calculation field "Gross profit rate", as shown in the figure below:

4) Drag "Gross profit rate" into the color bar, click the color bar, select area gradient>custom, set the area gradient area and the corresponding color, as shown in the following figure:

5) Set the label.

Drag "Gross profit rate" and "Province" into the label column, click on the label column, and choose to display only the max/min labels, as shown in the following figure:

6) Name the component "Regional gross profit rate analysis", and tick it to display all data, as shown in the figure below:

You can easily find out which area has abnormal gross profit margin through the map, and you can continue to locate more accurate stores below.

1) Use "Commodity Sales Summary Table" to create another component.

2) Create the "Gross profit rate" calculation field again, drag it to the horizontal axis, and drag the "Store" field to the vertical axis, and arrange the stores in ascending order of gross margin, as shown in the following figure:

3) Check to view all data, drag the "Gross Profit Rate" field into the color bar, consider that the gross profit rate is less than 10% is abnormal, and it will be displayed in red, as shown in the following figure:

4) Set the label, drag "Gross Profit Rate" into the label, and set the numeric format to percentage, as shown in the figure below:

5) Enter the component style, set not to display the legend, as shown in the following figure:

6) Change the title of this component to "Analysis of store gross profit rate".

Since we focus on the abnormal area that needs to be viewed in August, we use the date filter component to filter.

1) Enter the dashboard and drag in the "Year-Month" filter component. As shown below:

2) Enter the detailed setting interface of the filter component, bind the date field of the "Commodity Sales Summary Table" for the filter component, and click OK, as shown in the figure below

3) Set the control range.

Click on the "Year-Month" component, and select the drop-down> Custom Control Range, as shown in the figure below:

Check the "Regional gross profit analysis" and "Analysis of store gross profit rate" components, so the "Year-Month" filter component only has filtering control functions for these two components. As shown below:

Setting the "Regional gross profit analysis" component only has a linkage filter effect for "Analysis of store gross profit rate".

1) Click on the component, select drop-down> linkage settings, uncheck the darkened component will no longer be affected and controlled by the "Regional gross profit analysis" component, as shown in the following figure:

2) Click OK to save the linkage configuration.

The effect of quick positioning operation is shown in the figure below.

1) Filter out the data for August;

2) Found that the gross profit margin of Hunan Province was abnormal in August, click "Hunan Province>Drill Down" in the map to drill down to the city, you can see that the gross profit margin of Changsha City in Hunan Province is abnormal;

3) Click on Changsha City and filter out all the stores in Changsha City in the "Analysis of store gross profit rate". It can be observed that the gross profit margin of the "Changsha Meixi Lake Store" is -0.14;

In the end, we found the store that caused the abnormal gross profit margin in August.

At this point, we have located the store with abnormal gross profit margin. If we want to continue to locate the product with abnormal gross profit margin, the date of occurrence, and the specific order number, we can continue the analysis following the video:

Regional store analysis & product quadrant analysis, abnormal order monitoring