In this tutorial, we’ll walk you through the process of creating an insightful dashboard to compare the price, cost, and profit of various products. We’ll use Excel and Power BI to extract and visualize the data. Follow along to learn how to transform raw information into valuable insights.
To begin, open the Excel file in Power BI using the ‘Get Data’ option. In the navigator window, select the ‘Products’ table and load the data into Power BI Desktop.
Now that the data is loaded, we can leverage these fields to generate compelling visuals. However, since the table contains multiple models of the same product, we’ll focus on aggregating data for products with multiple models. The model numbers are indicated by a “-“.
To identify the position of the “-“, we initially attempted to use the ‘FIND’ formula, but encountered errors. A similar issue arose when using the ‘SEARCH’ formula. We resolved this by utilizing the ‘LENGTH’ formula instead of zero. This approach returns the number of characters when the character we’re searching for isn’t present.
Both ‘SEARCH’ and ‘FIND’ produced identical results, prompting us to retain one and discard the other. We then employed the ‘LEFT’ formula to extract the product names, excluding unwanted characters like the “-“.
Further refinement was needed as the product name itself contained “-“, which we wanted to eliminate. By adjusting the ‘FIND’ formula and accounting for the length, we successfully removed the unnecessary characters, resulting in accurate product names.
Now that our data is refined, let’s proceed to create our dashboard. We’ll start by adding a title and then incorporate a column and line chart to analyze the average price and cost of products.
For the y-axis of the chart, we’ll plot the average price and cost fields. The x-axis will display the product names. We’ll duplicate this chart to analyze the average cost and profit, with the y-axis showing the average cost and profit fields.
Next, we’ll introduce a pie chart to visualize the total profit earned by each product. The legend area will include the product names, and the values area will display the sum of profits.
With these visuals in place, our dashboard is complete. We hope you found this tutorial helpful in harnessing the power of Excel and Power BI to transform data into meaningful insights. If you enjoyed this tutorial, please consider liking and subscribing for more insightful tutorials and updates.
Thank you for watching, and we look forward to bringing you more valuable content in the future!
Power BI file: https://drive.google.com/file/d/1t74XnNUvk21fjawMfCzPoV0Mypao6Bjn/view?usp=sharing
#DataVisualization #ExcelTutorial #PowerBIDashboard #DataAnalysis #BusinessIntelligence #DataInsights #VisualAnalytics #DashboardDesign #ProductAnalysis #Tutorial #DataDriven #BusinessAnalytics #DataPresentation #ExcelTips #PowerBIInsights