Achieve an End-to-End Data Analysis for your data in Fabric Part 2

[Someleze]

After ingesting, cleaning and transforming data, it is essential to visualize it in order to explore its patterns, trends and relationships. Visualizing data can help identify outliers, anomalies, clusters and correlations that might not be evident from numerical or textual data alone. Data visualization can also facilitate the communication and presentation of the results of data analysis to different audiences, such as stakeholders, customers or decision-makers. By using appropriate charts, graphs, maps or dashboards, data analysts can convey insights and recommendations in a clear and engaging way.

A scenario for Contoso Cuisines

One of the goals of Contoso Cuisines is to visualize the data they ingested and cleaned in Fabric to gain insights about their business performance. They want to see how much revenue they generated from orders in different regions, countries and time periods. They also want to compare the revenue based on the products they sold, such as categories, units and prices. Moreover, they want to evaluate the employee performance by looking at the number of orders, customers and commissions they handled. They can achieve this by completing the following step:

1. Data Visualization
   1. Overview report of revenue generated
   2. Revenue performance report based on products
   3. Employee performance report based on orders and revenue generated

A - Overview report

As part of the requirements, the company wants a report that contains an overview of the revenue generated from orders in different countries, time periods and the impact of shipping days on the revenue. To achieve this, you can do the following:

• In Microsoft Fabric, navigate to Power BI using the options on the left bottom corner of the page. Once on the Power BI page select the New Report button then select the Pick a published semantic model option
  
• Choose your published semantic model from the options presented to you then select the Connect button and choose Create a blank report.
  Note: use the semantic model that has your Contoso Cuisines dataset set up from the previous blog (part 1)
• On the blank page, add a Filter visualization at the top for Category and Product. Set the Field property by dragging and dropping CategoryName and ProductName from the Products table. Change the style of the visualization to be a Dropdown list.
• Add 2nd Filter visualization for Country and City. Set the Field property by dragging and dropping Country and City from the Order_Details_clean table. Change the style of the visualization to be a Dropdown list.
• Add a 2nd Filter visualization for Country and City. Set the Field property by dragging and dropping Country and City from the Order_Details_clean table. Change the style of the visualization to be a Dropdown list.
• Add a 3rd Filter visualization for Title and Employee. Set the Field property by dragging and dropping Title and FirstName from the Order_Details_clean table. Change the style of the visualization to be a Dropdown list.
• Add the last Filter visualization for Order Date. Set the Field property by dragging and dropping OrderDate from the Order_Details_clean table. Change the style of the visualization to be Between.
• Add 5 Card visualizations below the filters and set the Field properties to the following
  • Card 1 – GrossRevenue
  • Card 2 – Discount Value
  • Card 3 – NetRevenue
  • Card 4 – Orders
  • Card 5 – Quantity
• Add a Gauge visualization and set its properties to the following values:
  • Value – Average days to ships (use the DaysToShip field)
  • Minimum value – Minimum of Days to ship
  • Maximum value – Maximum of Days to ship
• The top part of your report page should look something similar to this
  
• You need to get the revenue generated based on each country. Add a Map visualization and set the properties to the following values:
  • Location – use the Country and City fields from the Order details table.
  • Bubble Size – Net_Revenue field
• You need to visualize the sales revenue performance and the frequency of orders over a period of 12 months. To achieve this, add a Line and Clustered Column Chart and set the properties to the following
  • X-axis – Month
  • Column y-axis – Orders (the measure)
  • Line y-axis – Gross_Rvenue
• You need to analyse the customer's shipping company choice and the average days that company ships orders to customers. To achieve this, add a Funnel visual and set the Category property to the Shipper_Company field and then set the Values property to the average of Days_to_ship.
• Once done, this should be the result of your report page. Rename it to Overview
  

B – Revenue Performance based on Product

The next part of the requirements is to enable the company to analyse the sales performance to identify which products generate more impact to the revenue generated and is the most popular among customers.

• In your Power BI report, duplicate the Overview page and remove all the Card visualizations, Map, Gauge, Line and Clustered Column Chart, and Funnel visualizations.
• First, you need to visualize the top 5 Products based on the number of Orders made for those products. To achieve this, add a Clustered bar chart and set the Y-axis property to the ProductName field from the Products table then set the X-axis property to the Orders measure.
• Make sure to set the filter of the visualization to show the Top N and set the number items to 5.
• Then, you need to visualize the bottom 5 Products based on the frequency of Orders made. To achieve this, copy the Top 5 Products visualization and update the filter to show the bottom 5 items.
• Next, you need to visualize the actual Discount, Gross and Net Revenue generated by orders for each Category. To achieve this, add a Matrix visualization and set the following properties
  • Rows – use CategoryName and ProductName fields
  • Values – use the Orders measure, Quantity,  Gross Revenue, Discount and Net Revenue fields.
• You can some conditional formatting to show the variation in values for the Gross Revenue and Net Revenue, for example this will show how well each product category is performing based on the revenue it generates. To achieve this, select the dropdown icon next to Gross Revenue field, choose conditional formatting then choose data bars.
• Once done, the first part of the report page will look something like this
  
• Next, you need to show the how much Units are in Stock vs Units that are on order for each Product Category. You can achieve this by adding a Matrix visualization and set the properties to the following
  • Rows – use CategoryName and ProductName fields
  • Values – use the UnitsInStock and UnitsOnOrder fields
• Lastly, to complete this page you need to show case the Units in stock for each Product Category on it’s own visual for a better analysis. To achieve this, add a Clustered Column Chart and set the properties as the following
  • X-axis – use CategoryName and ProductName fields
  • Y-axis – use the UnitsInStock field
• Rename your page to Category and Product Performance and it should look similar to this
  

C – Employee Performance

The last part of the requirements if for the company to be able to analyze and monitor their employee performance to look at things like how much revenue is generated by each Employee role designation.

• In your Power BI report, duplicate the Category and Product Performance page.
• First, you need to visualize the top 5 Employees based on the number of Orders made under their influence. To achieve this, update Top 5 Products Clustered bar chart and set the Y-axis property to the FirstName field from the Order Details table then set the X-axis property to the Orders measure.
• Make sure to remove the ProductName filter and set the filter of the visualization to show the Top N and set the number items to 5 on FirstName.
  
• Then, you need to visualize the bottom 5 Employees based on the frequency of Orders made. To achieve this, update the Bottom 5 Products visualization and update the filter to show the bottom 5 items on FirstName and not ProductName
• Next, you need to visualize the actual Discount, Gross and Net Revenue generated by orders from each Employee Title and Employee Name. To achieve this, update the Matrix visualization and set the following properties
  • Rows – use the Title and FirstName fields
• Now, you need to visualize the Net Revenue generated by each individual Employee Title/Department to identify any decrease/increase in the revenue performance. To achieve this, update the bottom Matrix visual and make it into a Waterfall Chart then set the properties to the following
  • Category – use the Title field
  • Y-axis – use the Net Revenue field
• Lastly, you need to analyze the Net Revenue Per Order generated by each individual Employee as part of their performance analysis. To achieve this, update the Clustered Column Chart and set the properties to the following
  • X-axis – use the FirstName field
  • Y-axis – use the Net Revenue per order field
• Once done, your Employee Performance report page should look something like this
  

Power BI report credit: Power BI Dashboard with Northwind 

Homework

Now that you have went through an example of building a simple end-to-end solution with Fabric, you’re tasked with:

1. Using the same dataset to train machine learning models with Data Science in Microsoft Fabric to predict future sales performance and customer behavior.
2. Following the same solution structure with your own dataset this time and tag me on LinkedIn to share your solution!
3. Get certified with the Microsoft Fabric Analytics Engineer DP:600 exam