Dashboards: Visualizing the data
previous blog : Data Modeling: Streamlining the Data Flow
Dashboard 1 - Revenue Analysis
Dashboard 2 - Product Sales Analysis
Dashboard 3 - Retailer & Supplier Performance
KPI (Key Performance Indicator):
It is a measure used to evaluate the performance of an organization or a specific aspect of a business. KPIs help track progress towards goals and provide valuable insights for decision-making.
In Power BI, KPIs are visualized using indicators such as icons, gauges, or data bars to display performance status at a glance.
DAX (Data Analysis Expressions):
It is a formula language used in Power BI and other Microsoft products like Excel and SQL Server Analysis Services (SSAS).
DAX allows users to create custom calculations and expressions to manipulate data, perform calculations, and create complex measures and calculated columns.
We have used DAX to create KPIs, measures, and columns, enabling us to gain deeper insights and make data-driven decisions with ease.
Revenue Analysis Dashboard :
In the Revenue Analysis dashboard, we focused on five key performance indicators (KPIs):
- Revenue: This KPI shows the total revenue earned by the company.
- Revenue % change: It represents the percentage change in revenue compared to the previous period, indicating revenue growth or decline.
- Revenue Last year: This KPI displays the revenue generated during the same period last year, providing a year-on-year comparison.
- Total Products: It indicates the overall number of products sold.
- Total Retailers: This KPI represents the total count of retailers associated with the company.
Chart 1 - Revenue by Quarter:
- We used a line chart to visualize the trend of revenue across different quarters.
- The X-axis represents the date table with quarters, months, and days.
- The Y-axis shows the revenue measure.
- This allows the user to drill up and down to explore revenue trends at different time granularities.
- We also added a tooltip to display the top-performing products for each day, month, quarter, and year, providing additional insights.
- We created the date table using ‘CALENDAR’ function in DAX which created a date range starting from the earliest date and ending with the latest date in our dataset.
Date = CALENDAR(MIN(fact_order1[ Invoice Date]),MAX(fact_order1[ Invoice Date]))Next, we added additional columns to the date table, such as year, quarter, month, day, using DAX functions like ‘YEAR’, ‘QUARTER’, ‘MONTH’, and ‘DAY’.
Year :
Year = YEAR('Date'[Date])Quarter :
Quarter = FORMAT(DATE(1, 'Date'[Month],1),"\Qq")Month name:
Monthname = FORMAT(DATE(1,[Month],1),"mmm")Month:
Month = MONTH('Date'[Date])Day:
Day = FORMAT('Date'[Date],"ddd")Year Hierarchy :
Revenue Dax :
Revenue = CALCULATE(SUM(fact_order1[ Product Level NetAmount]))To calculate the revenue measure, we utilized the ‘Total Level Net Amount’ column from our dataset. The formula sums up the net amounts for each product.
Chart 2 - Total Revenue by Company:
- To understand the total revenue generated by each company, we utilized a donut chart.
- The legend shows the brands (“Godrej”, “Marico” and other), and the values represent the revenue measure for each brand.
- We created a conditional column(named “Brand”) to categorize products based on their codes, enabling us to differentiate between “Godrej,” “Marico,” and other brands.
Chart 3 (a) - Top Revenue Generating Products:
- Using a clustered column chart, we displayed the top revenue-generating products.
- The X-axis shows the product names, and the Y-axis presents the revenue generated by each product.
- To provide user flexibility, we added a slider to control the number of products displayed. This way, the user can focus on the most relevant products.
Top N Products DAX :
Top Products = IF([Rank Product]<= 'Top N for Products'[Top N for Products Value],[Revenue],BLANK())Chart 3 (b) - Top Revenue Generating Retailers:
- Similar to the previous chart, this clustered column chart shows the top revenue-generating retailers.
- The X-axis represents retailer names, and the Y-axis displays the revenue generated by each retailer.
- Again, we added a slider to allow users to choose the desired number of top retailers to view.
Top N Retailer DAX :
Top Retailer = IF([Rank Retailer]<= 'Top N for Retailers'[Top N for Retailers Value],[Revenue],BLANK())Steps for creating a dynamic slider:
Step 1 - Rank Product:
In this step, we used the DAX measure “Rank Product” to rank the products based on a specific condition, in this case, revenue. The formula used is:
Rank Product = RANKX(ALL(Dim_product[ Product Name]),[Revenue], ,DESC)The RANKX function is used to rank each product in the ‘Dim_product’ table based on the ‘Revenue’ measure in descending order (indicated by the “DESC” argument). The ALL function is used to remove any existing filters on the ‘Dim_product[Product Name]’ column, ensuring that the ranking is performed irrespective of any other filters applied to the report.
Step 2 - Numeric Parameter:
In this step, a numeric parameter was created in the “Modeling” tab of Power BI, allowing the user to control the number of products displayed on the slider.
Top N for Products = GENERATESERIES(1, 20, 1)Step 3 - Top Products:
The “Top Products” measure is used to show only those products that fall under the number selected on the slider.
Top Products = IF([Rank Product]<= 'Top N for Products'[Top N for Products Value],[Revenue],BLANK())The measure “Top Products” utilizes an IF statement to determine if the rank of each product is less than or equal to the numeric value selected on the slider, which is stored in the ‘Top N for Products’[Top N for Products Value] field. If the condition is true, the measure returns the ‘Revenue’ value for that product; otherwise, it returns a BLANK value, effectively hiding the product from the visualization.
Similarly, we created a slider to display the top retailers based on their performance.
Top Retailer = IF([Rank Retailer]<= 'Top N for Retailers'[Top N for Retailers Value],[Revenue],BLANK())Top N for Retailers = GENERATESERIES(1, 20, 1)Rank Retailer = RANKX(ALL(Dim_retailer[ Retailer Name]),[Revenue], ,DESC)We followed these steps throughout the rest of the blog to create sliders for various other analysis as well.
Chart 4 - Performance Summary By Time Period:
- In this section, we calculated three significant performance metrics — YTD, MTD, and QTD.
- The table chart displays these metrics, which help track the company’s revenue performance within specific time intervals.
- The YTD column represents the revenue accumulated from the beginning of the year to the current date, the MTD column shows the revenue within the current month, and the QTD column indicates the revenue within the current quarter.
MTD:
The ‘DATESMTD’ function is used in combination with ‘CALCULATE’ to filter the data to only include dates from the beginning of the current month up to the selected date.
MTD = CALCULATE(SUM(fact_order1[ Product Level NetAmount]),DATESMTD('Date'[Date].[Date]))QTD:
The ‘DATESQTD’ function is used in combination with ‘CALCULATE’ to filter the data to only include dates from the beginning of the current quarter up to the selected date.
QTD = CALCULATE(SUM(fact_order1[ Product Level NetAmount]),DATESQTD('Date'[Date].[Date]))YTD:
The ‘DATESYTD’ function is used in combination with ‘CALCULATE’ to filter the data to only include dates from the beginning of the current year up to the selected date.
YTD = CALCULATE(SUM(fact_order1[ Product Level NetAmount]),DATESYTD('Date'[Date].[Date]))Product Sales Analysis Dashboard:
Chart 1 - Total Quantity By Year:
- With a line chart, we visualized the trend of quantity sold by year.
- The X-axis represents the date hierarchy, allowing users to drill up and down to explore quantity trends at different levels, such as quarter, month, and day. This provides valuable insights into product sales patterns over time.
- The Y-axis represents the “Total Quantity,” displaying the quantity of products sold.
Chart 2 - Top Selling Products By Quantity:
- Using a slider-controlled clustered column chart, we highlighted the top-selling products based on the quantity sold.
- The X-axis shows the “Product Name,” listing the names of products, while the Y-axis displays the “Top Products by Quantity” Measure.
Top Products by Quantity = IF([Rank Product]<= 'Top N Products By Quantity'[Top N Products By Quantity Value],[Total Quantity],BLANK())Rank Product = RANKX(ALL(Dim_product[ Product Name]),[Revenue], ,DESC)Chart 3 - Products with Highest Return Count:
- Using a bar chart, we identified products with the highest return rate for both brands.
- To assess return rates, we calculated the “Return to Purchase Ratio” measure using the formula (Quantity Returned / Quantity Sold).We converted this ratio into a percentage for better understanding and interpretation.
Return to Purchase Ratio = CALCULATE(DIVIDE([Total Returns],[Act Total Quantity Viz]))- The X-axis displays the “Top Products Return” measure, while the Y-axis represents the product names.
- To showcase the analysis for both brands, we utilized the bookmark feature of Power BI. By using bookmarks, we can easily toggle between “Godrej” and “Marico” to compare their respective product return rates.
- By analyzing this rate, we can identify products that experience a relatively higher proportion of returns compared to their total sales quantity.
- Additionally, we added a tooltip that shows “total quantity sold by year”, providing additional information about the total quantity sold for that product in each year.
Top Products Return = IF([Rank Product Return]<= 'Top N for Product Return'[Top N for Product Return Value],[Return to Purchase Ratio],BLANK())Rank Product Return = RANKX(ALL(Dim_product[ Product Name]),[Return to Purchase Ratio], ,DESC)Chart 4 - Brand-wise Quantity Sold:
- Presented as a pie chart, this visualization displays the quantity sold by each brand.
- The legend represents the different brands (“Godrej”, “Marico” and other), and the values show the total quantity sold for each brand
Retailer & Supplier Performance Dashboard:
Chart 1 - Supplier and Retailer Quantity Comparison Over Time:
- Visualized as an area chart, this chart presents the quantity bought from suppliers (from the supplier table) and the quantity sold to retailers (from the retailer table) over time.
- The X-axis represents the date hierarchy, allowing users to drill up and down to explore quantity trends at different time intervals, such as quarter, month, and day.
- The Y-axis represents the quantity of products, showcasing the volumes of products bought from suppliers and sold to retailers.
- This chart provides a comprehensive view of the retailer-supplier transaction dynamics, highlighting patterns and trends in the quantities bought and sold.
Chart 2 - Top 5 Selling Products by Brand:
- Presented as a table chart, this visualization displays the top 5 selling products for each brand, along with their total quantity sold.
- The table includes three values: “Brand,” “Product Name,” and “Total Quantity.”
- We added a Top 5 filter option for products. Additionally, to enhance user experience, we used bookmarks to show the top 5 products for each brand separately. This allows users to quickly switch between the top products for “Godrej” and “Marico,” gaining insights into the best-selling items for each brand.
Chart 3 - Number of Orders from Top Retailers:
- Presented as a slider-controlled bar chart, this visualization illustrates the number of orders from the top retailers.
- The X-axis represents retailer names, while the Y-axis displays the “Top Retailers By Orders” measure, which shows the number of orders placed by each retailer.
- We added a tooltip to provide further insights. The tooltip shows the number of orders for each retailer, broken down by brand. The X-axis of the tooltip represents different brands, while the Y-axis shows the corresponding number of orders for each brand. This information helps to understand the distribution of orders among different brands for each retailer.
Top Retailer by orders = IF([Rank Retailers by invoice count]<= 'Top N for Retailers by invoice count'[Top N for Retailers by invoice count Value],[No of Orders],BLANK())Rank Retailers by invoice count = RANKX(ALL(Dim_retailer[ Retailer Name]),[No of Orders], ,DESC)No of Orders = CALCULATE(COUNT(fact_order1[ Invoice Number]),ALLEXCEPT(Dim_retailer,Dim_retailer[ Retailer Name]))- The “No of Orders” measure calculates the count of unique invoice numbers in the ‘fact_order1’ table, representing the total number of orders placed.
- The “ALLEXCEPT” function removes all filters from the “Dim_retailer” table except for the one applied to the “Retailer Name” column. This ensures that the count of orders is calculated for each retailer individually, irrespective of other filters applied to the report.
Chart 4 - Quantity of Brand Purchased By Retailer:
- Using a treemap, we visualized the quantity of each brand purchased by retailers.
- The category represents retailer names, details represent brands, and the value represents the sum of total quantity. This chart allows for a quick overview of retailer purchases for different brands.
The dashboard’s background was thoughtfully designed using Figma, creating a visually appealing and user-friendly experience to facilitate data interpretation and decision-making.
Next blog : Insights: Unveiling the Secrets of Analysis