Case Study : Analysis of Mandarin Prices with Power BI

9 min readSep 25, 2023

Mandarins contain Vitamins A, B, and a high level of Vitamin C, a powerful antioxidant that neutralizes free radicals, prevents infections, cramps, and vomiting, and is great for the health of your skin.

Mandarin oranges were introduced to the West at the turn of the 19th century. In 1805, two varieties of the Mandarin oranges were brought into England from Canton, China. From England, it was then introduced to the Mediterranean region.

The demand for oranges rose sharply this year due to the coronavirus. One would expect that mandarins would also benefit from this higher demand, but that wasn’t the case. In fact, in some countries, such as the US and South Africa, mandarins were somewhat overshadowed by the success of oranges. Still, the demand for mandarins remains stable. Especially in the US and Europe, the prospect is that the demand will increase ahead of the winter. As far as the supply is concerned, there are few large sizes from Spain. The quality of the mandarins from China has been seriously affected by heavy rainfall.

In this blog, I am going to explain how I have completed a case study over the mandarin sold at subsequent prices in various regions using Power BI.

Data Set:

The data set has 14 columns Some relevant columns in the dataset:

● Date — The date of the observation

● Average Price — the average price of a single mandarin

● Type — conventional or organic

● Year — the year

● Region — the city or region of the observation

● Total Volume — Total number of mandarins sold

● 4046 — Total number of mandarins with PLU 4046 sold

● 4225 — Total number of mandarins with PLU 4225 sold

● 4770 — Total number of mandarins with PLU 4770 sold

Learning Objective

The primary objective of this case study are the following things:

1. Mandarins Price Analytics project with sample visuals

2. How to create Histogram

3. How to create an Infographic view with customized image

4. How to create a Lollipop Bar Chart

5. Customize theme

Import Data

Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list

Select the file named mandarin.csv

After selecting the file, data will be displayed with options Load, Transform Data.
Click on Transform Data and Power Query Editor will open.

Transform Data

In Power Query editor, you need to do the following changes.

Click on “Use First Row as Headers” from the Transform group under the Home tab and observe that the table header is created.

2. Give a proper name for column 1, for example, SR No.

3. Rename the last 3 column names as Type, Year and Region.

4. Click on Close and Apply to save.

5. Update Summarization properties for Average Price from Sum to Average and Format from General to Decimal Number.

6. Change to “Don’t Summarize” for SL No and Year.

7. Update data formatting to decimal number for all the measured fields.

Data Visualization

Power BI offers different advanced visuals. These are all supported by 3rd party providers.
We need to first import required visuals from All Visuals after clicking the “Get more visuals” under the visualization section.

Get More Visuals-

Click on Get more visuals under the Visualization section then click on Get more visuals out of the displayed 4 options.

2. Power BI Visuals box opens. Please check, by default the All Visuals tab should be selected.

3. Now in the search box, first type “Infographic” and click on the search icon. Select the first Infographic Designer view

4. Similarly, you get the other two custom visuals ” Lollipop Bar” and “Histogram”.

Create Histogram, Line Chart, Donut Chart, Lollipop Bar Chart, Card, Slicer and Infographic Designer view:

Creation of the Histogram-

To create a Histogram, please follow the below steps.

1. Select Histogram visual from the panel.

2. Add Total Bags in the Values and Frequency section.

3. Click on the Format section and update Display Units for X-axis, Y-axis. Display Data Label and enter Analysis based on total bags as the title with some formatting.

Observation: In this graph the X axis consists of the range of mandarins produced as per the count of a bag. The highest amount of bags produced is 47.58k to 52.34k and which can be sold in total at the cost of 19.76M.
Mandarin oranges vary in color and flavor. They are easy to peel and last for 1–2 weeks in a cool place. People can also keep them for longer in the refrigerator or freezer.

Creation of Line Chart:

For the Line chart, please follow the below steps.

1. Keep Month from Date hierarchy in Axis.

2. Add the Type in Legend and Average Price in Values

Observation: The sale prices are high depending on the months so as the price varies depending on the months.
The organic mandarin can cost $1.85 in the month of September which is the highest selling price and $1.53 is the lowest selling price which is for the February month.

The conventional mandarin can cost $1.35 in the month of October which is the highest selling price and $1.02 is the lowest selling price which is for the February month.

Creation of Donut Chart:

For this chart, add the Type in legend and Average Price in values.

Observation: Firstly let us understand the meaning of Organic and Conventional farming of Mandarins.

ORGANIC — Organic farming is a method of agriculture that emphasizes the use of natural and sustainable practices. It avoids the use of synthetic pesticides, synthetic fertilizers, genetically modified organisms (GMOs), and antibiotics in livestock. Instead, it relies on natural fertilizers, crop rotation, and biological pest control methods.

CONVENTIONAL — Conventional farming, also known as industrial or traditional farming, often involves the use of synthetic chemicals, pesticides, herbicides, and synthetic fertilizers to maximize crop yields. GMO crops are commonly grown, and livestock may receive antibiotics and growth hormones.

Now as per the observation from the above Donut chart, the average price of Mandarins produced by organic is $1.65 and 58.82 percent of the market is covered by organic Mandarins. Coming to conventional, the average price of Mandarins produced by conventional is $1.16 and 41.18 percent of the market is covered by conventional Mandarins.

Creation of Lollipop Bar Chart:

To create a Lollipop Bar chart, please follow the below steps.

1. Select Lollipop Bar Chart visual from the pane.

2. Add Region in Axis, Average Price in Values and Total Bags in Tooltips box

3. Copy the same formatting from the above chart using Format painter.

Observation: There are 53 regions where Mandarins are sold and in this graph the average price of Mandarin sold in the specific region is mentioned. The lowest average prices of Mandarins are $1.05 which are sold in the Houston region and the highest average prices of Mandarins are $1.82 which are sold in the Hartford Springfield region which consists of 11333012.63 total bags.

Creation of a Card View:

To create a card, please follow the below steps.

1. Select Card visual from the pane.

2. Add Average Price in the field.

Observation: This card shows the Average price of the Mandarins are sold in the market. The highest rate of the mandarin sold is at 3.25 and the lowest rate of the mandarin sold is at 0.44 with a total volume of $ 4.77B. The average mandarin export price amounted to $1121 per ton, falling by 25.8% against the previous year however the trend patter indicated some noticeable fluctuations being recorded throughout the analyzed period.

Creation of Slicer:

To create an Slicer, please follow the below steps.

1. Select Slicer from the pane.

2. Add Type and Year in the Field.

Observation: This Slicer gives a filtering option to set the dashboard as per type and year. This way we get the see the sales of mandarin and areas sold at higher and cheaper rate of both conventional and organic. The filter helps to identify the total bags sold at a specific rate for the particular year. This way we can promote more sales in the particular region for the upcoming years.

Creation of Infographic Designer View:

To create an Infographic view, please follow the below steps.

1. Select Infographic Designer visual from the pane.

2. Add Year in Category, Type in Legend and Total Volume in Measure box.

Observation: This graph gives an explanation on the total volume of Mandarins sold in the particular year along with the type of mandarins sold which can be organic or conventional. The highest gross selling year is 2017 where organic Mandarins went at the total volume of $ 168.14M and conventional Mandarins went at the total volume of $ 4.77B.

Conclusion:

● Mandarins are a good source of vitamin C and phytonutrients and have antioxidant and anti-inflammatory benefits.

● Mandarins are grown in two types which are organic and conventional.

● Mandarins are sold in multiple regions but mainly it’s sold in 53 regions at a different rates as per the regions.

● The growth of organic mandarins volume is less compared to conventional due to various reasons.

● The sales of conventional mandarins are very high compared to organic mandarins since they are grown more in volume.

● The Average highest price of Mandarins is sold at Hartford Springfield for $ 1.82 which is the best rate in 53 regions.

● The average mandarin export price amounted to $1121 per ton, falling by 25.8% against the previous year however the trend patter indicated some noticeable fluctuations being recorded throughout the analyzed period.

● Using the Histogram bar graph we are able to identify the sales of a bag which consists both organic and conventional mandarins.