Data Collection and Preparation in DAX

Set Up Instructions

1. Open Microsoft Power BI Desktop.
2. Connect to your retail data source (Excel, SQL Server, etc.).

Steps for Data Collection and Preparation

Data Loading

// Load Sales Data from a CSV file
SalesData = 
    DETAILROWS (
        CSVSource( "PathToCSVFile" )
    )

// Load Product Data from a database
ProductData = 
    IMPORT( 
        "SELECT * FROM ProductsTable", 
        DatabaseConnectionDetails 
    )

Data Cleaning

// Removing duplicates
CleanedSalesData =
    DISTINCT(SalesData)

// Filtering out invalid entries
ValidSalesData =
    FILTER(
        CleanedSalesData,
        NOT (
            ISBLANK([SalesAmount]) 
            || [SalesAmount] < 0
            || ISBLANK([ProductID])
            || ISBLANK([TransactionDate])
        )
    )

Data Transformation

// Adding calculated columns
SalesData =
    ADDCOLUMNS(
        ValidSalesData,
        "Year", YEAR([TransactionDate]),
        "Month", FORMAT([TransactionDate], "MMM"),
        "Quarter", "Q" & FORMAT([TransactionDate], "Q")
    )

// Joining product information
CombinedData =
    NATURALINNERJOIN(
        SalesData,
        ProductData
    )

Data Aggregation

// Aggregating sales by product and time
SalesSummary = 
    SUMMARIZE(
        CombinedData,
        [ProductID], [Year], [Quarter], [Month],
        "TotalSales", SUM([SalesAmount]),
        "TotalUnitsSold", SUM([UnitsSold])
    )

Data Loading and Cleansing

// Loading the data into the Power BI model
LOAD (
    "Sales Summary",
    SalesSummary,
    Headers
)

Example Measures

// Total Sales Measure
TotalSales_Measure = 
    SUM(SalesSummary[TotalSales])

// Total Units Sold Measure
TotalUnitsSold_Measure = 
    SUM(SalesSummary[TotalUnitsSold])

Example Visualizations

// Create a bar chart for total sales by month
BarChart =
    CALCULATE(
        TotalSales_Measure,
        TREATAS(
            VALUES(CombinedData[Month]), 
            SalesSummary[Month]
        )
    )

// Create a line chart for total units sold by year
LineChart =
    CALCULATE(
        TotalUnitsSold_Measure,
        TREATAS(
            VALUES(CombinedData[Year]), 
            SalesSummary[Year]
        )
    )

Sales Performance Analysis Using DAX

Define Calculated Columns and Measures

Step 1: Calculate Total Sales

Total Sales = SUM(Sales[Revenue])

Step 2: Calculate Sales Growth Rate

Sales Growth Rate = 
VAR PreviousPeriodSales = 
    CALCULATE (
        [Total Sales],
        SAMEPERIODLASTYEAR(Sales[Date])
    )
RETURN
    DIVIDE([Total Sales] - PreviousPeriodSales, PreviousPeriodSales, 0)

Step 3: Calculate Total Profit

Total Profit = SUM(Sales[Profit])

Step 4: Calculate Profit Margin

Profit Margin = 
DIVIDE(
    [Total Profit],
    [Total Sales],
    0
)

Step 5: Top 5 Performing Products

Top 5 Products = 
TOPN(
    5,
    VALUES(Product[ProductName]),
    [Total Sales],
    DESC
)

Step 6: Sales by Category

Sales by Category = 
SUMMARIZE (
    Sales, 
    Category[CategoryName], 
    "Category Sales", [Total Sales]
)

Step 7: Total Sales by Month

Sales by Month = 
SUMMARIZE (
    Sales, 
    'Date'[MonthYear], 
    "Monthly Sales", [Total Sales]
)

Step 8: Average Order Value

Average Order Value = 
DIVIDE(
    [Total Sales], 
    DISTINCTCOUNT(Sales[OrderID]), 
    0
)

Step 9: Sales per Region

Sales by Region = 
SUMMARIZE (
    Sales, 
    Region[RegionName], 
    "Region Sales", [Total Sales]
)

Step 10: Customer Lifetime Value

Customer Lifetime Value = 
VAR TotalCustomerSales = 
    CALCULATE (
        [Total Sales],
        ALL (Sales),
        VALUES(Customer[CustomerID])
    )
RETURN
    DIVIDE(
        TotalCustomerSales,
        DISTINCTCOUNT(Customer[CustomerID]),
        0
    )

Step 11: Churn Rate

Churn Rate = 
VAR PreviousPeriodCustomers = 
    CALCULATE (
        DISTINCTCOUNT(Customer[CustomerID]),
        SAMEPERIODLASTYEAR(Sales[Date])
    )
VAR CurrentPeriodCustomers = 
    DISTINCTCOUNT(Customer[CustomerID])
RETURN 
    DIVIDE(
        PreviousPeriodCustomers - CurrentPeriodCustomers,
        PreviousPeriodCustomers, 
        0
    )

Visualize Results

Sample Visualizations

• Total Sales: Bar chart by month and category.
• Profit Margin: KPI card to show overall profit margin.
• Sales Growth Rate: Line chart by month/year.
• Top 5 Products: Bar chart.
• Sales by Region: Map visual.
• Churn Rate: Line chart by month.

These DAX formulas and visualizations will help analyze and optimize the sales performance of the retail company.

Inventory Management Insights

Step-by-Step DAX Formulas

1. Create Inventory Metrics Table

InventoryMetrics = 
SUMMARIZE(
    Sales,
    Products[ProductID],
    "Total Sales", SUM(Sales[SalesAmount]),
    "Total Units Sold", SUM(Sales[Quantity]),
    "Total Inventory Cost", SUM(Products[CostPrice] * Products[UnitsInStock]),
    "Remaining Stock", MAX(Products[UnitsInStock]) - SUM(Sales[Quantity])
)

2. Calculate Inventory Turnover Rate

InventoryTurnoverRate = 
DIVIDE(
    SUM('Sales'[Quantity]),
    AVERAGE(Products[UnitsInStock])
)

3. Measure Days of Inventory Outstanding (DIO)

DaysInventoryOutstanding = 
DIVIDE(
    [Total Inventory Cost],
    [Total Sales] / CALCULATE(COUNTROWS(Sales), Sales[Date])
) * 365

4. Calculate Stockout Rate

StockoutRate = 
DIVIDE(
    COUNTROWS(FILTER(Sales, Products[UnitsInStock] = 0)),
    COUNTROWS(Sales)
)

5. Measure Average Order Size

AverageOrderSize = 
AVERAGE(Sales[Quantity])

6. Calculate Gross Margin Return on Inventory (GMROI)

GMROI = 
DIVIDE(
    [Total Sales] - CCC(SUMX(Sales, Products[CostPrice])),
    [Total Inventory Cost]
)

7. Calculate Reorder Point

ReorderPoint = 
SUMMARIZE(
    'Sales',
    Products[ProductID],
    "Reorder Point", MAX(Products[LeadTime]) * AVERAGE(Sales[Quantity])
)

Deploying Insights on Dashboard

1. Create visualization tiles based on the above DAX metrics.
2. Arrange metrics for comprehensive visualization, enabling real-time inventory management insights.

Next Steps

1. Integrate insights into business intelligence tools for reporting.
2. Monitor dashboard for inventory optimization.

Part 4: Customer Behavior Analysis

Summary

This part focuses on analyzing customer behavior to optimize the operations of a large retail company using Data Analysis Expressions (DAX) in Power BI.

Measures

1. Total Customer Count:

TotalCustomerCount = DISTINCTCOUNT(Sales[CustomerID])

2. Repeat Customers:

RepeatCustomers = CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    FILTER(Sales, COUNTROWS(FILTER(Sales, Sales[CustomerID] = EARLIER(Sales[CustomerID]))) > 1)
)

3. First Purchase Date:

FirstPurchaseDate = CALCULATE(
    MIN(Sales[OrderDate]),
    ALLEXCEPT(Sales, Sales[CustomerID])
)

4. Last Purchase Date:

LastPurchaseDate = CALCULATE(
    MAX(Sales[OrderDate]),
    ALLEXCEPT(Sales, Sales[CustomerID])
)

5. Customer Lifetime Value (CLV):

CustomerLifetimeValue = 
SUMX (
    SUMMARIZE (
        Sales, 
        Sales[CustomerID], 
        "TotalAmount", 
        SUM(Sales[TotalAmount])
    ), 
    [TotalAmount]
)

6. Customer Segmentation - RFM (Recency, Frequency, Monetary):

// Recency: days since last purchase
Recency = DATEDIFF(
    MAX(Sales[OrderDate]), 
    TODAY(), 
    DAY
)

// Frequency: number of purchases
Frequency = COUNT(Sales[OrderID])

// Monetary: total spend by customer
Monetary = SUM(Sales[TotalAmount])

Calculated Columns/Table for RFM Segmentation

RFMTable = 
ADDCOLUMNS(
    SUMMARIZE (
        Sales, 
        Sales[CustomerID]
    ), 
    "Recency", [Recency],
    "Frequency", [Frequency],
    "Monetary", [Monetary],
    "RFMScore", [Recency] & [Frequency] & [Monetary]
)

Filters and Segments

// High Value Customers
HighValueCustomers = 
FILTER(
    RFMTable, 
    [RFMScore] IN {"555", "554", "544", "554"}
)

// Lost Customers
LostCustomers = 
FILTER(
    RFMTable, 
    [Recency] > 365  -- Assuming a customer is considered lost if they haven't purchased in over a year
)

Visualizations (Power BI Steps)

1. Customer Counts by Segments:

   • Create pie/bar chart using HighValueCustomers and LostCustomers.

2. CLV Over Time:

   • Line chart using CustomerID and CustomerLifetimeValue.

3. RFM Segmentation:

   • Table visualization using RFMTable to show individual customer segments.

Notes

• Utilize slicers in Power BI for dynamic filtering by segments (High Value, Lost, etc.).
• Ensure to use proper data relationships and model optimizations for clear visualization.

By directly implementing these DAX measures and visualizations, you will be able to gain actionable insights into customer behavior and make data-driven decisions to optimize operations.

Supply Chain Efficiency with DAX

1. Supplier Performance Analysis

SupplierPerformance = 
SUMMARIZE(
    'Orders',
    'Suppliers'[SupplierID],
    "TotalOrders", COUNT('Orders'[OrderID]),
    "TotalOrderValue", SUM('Orders'[OrderValue]),
    "DeliveryTime", AVERAGE('Orders'[DeliveryTime]),
    "OrderAccuracy", AVERAGE('Orders'[OrderAccuracy])
)

2. Lead Time Calculation

LeadTime = 
AVERAGEX(
    'Orders',
    DATEDIFF('Orders'[OrderDate], 'Orders'[DeliveryDate], DAY)
)

3. Order Fulfillment Ratio

OrderFulfillmentRatio = 
DIVIDE(
    COUNTROWS(FILTER('Orders', 'Orders'[Fulfilled] = TRUE)),
    COUNTROWS('Orders'),
    0
)

4. Inventory Turnover Ratio

InventoryTurnover = 
DIVIDE(
    SUM('Inventory'[CostOfGoodsSold]),
    AVERAGE('Inventory'[AverageInventory]),
    0
)

5. Demand Forecast Accuracy

ForecastAccuracy = 
1 - 
DIVIDE(
    SUMX('Sales', ABS('Sales'[ForecastedQuantity] - 'Sales'[ActualQuantity])),
    SUM('Sales'[ActualQuantity]),
    0
)

6. Delivery Performance

DeliveryPerformance = 
DIVIDE(
    COUNTROWS(FILTER('Orders', 'Orders'[DeliveryOnTime] = TRUE)),
    COUNTROWS('Orders'),
    0
)

7. Supplier Reliability

SupplierReliability = 
AVERAGEX(
    SUMMARIZE(
        'Orders',
        'Suppliers'[SupplierID],
        "AvgDeliveryTime", AVERAGE('Orders'[DeliveryTime]),
        "OnTimeDeliveries", COUNTROWS(FILTER('Orders', 'Orders'[DeliveredOnTime] = TRUE))
    ),
    DIVIDE(
        [OnTimeDeliveries],
        [AvgDeliveryTime],
        0
    )
)

8. Cost Per Unit Analysis

CostPerUnit = 
DIVIDE(
    SUM('Inventory'[TotalCost]),
    SUM('Inventory'[TotalUnits]),
    0
)

9. Overall Supply Chain Efficiency Score

SupplyChainEfficiencyScore = 
AVERAGEX(
    {
        [LeadTime],
        [OrderFulfillmentRatio],
        [InventoryTurnover],
        [ForecastAccuracy],
        [DeliveryPerformance],
        [SupplierReliability]
    },
    [Value]
)

Power BI Visualization Suggestions

• Supplier Performance Analysis: Use a matrix visual to display supplier metrics.
• Lead Time Calculation: Use a card visual to display the average lead time.
• Order Fulfillment Ratio: Use a gauge visual to show the fulfillment ratio.
• Inventory Turnover Ratio: Use a column chart to display turnover by product category.
• Demand Forecast Accuracy: Use a line chart to compare forecasted vs actual quantities.
• Delivery Performance: Use a pie chart to illustrate on-time vs late deliveries.
• Supplier Reliability: Use a scatter plot to map reliability scores.
• Cost Per Unit Analysis: Use a line chart or bar chart.
• Overall Supply Chain Efficiency Score: Display using a KPI visual.

This approach will help you track and optimize supply chain efficiency effectively. Apply the DAX formulas within your data model in Power BI to drive actionable insights.

Report Creation and Dashboard Development

Sales Performance Dashboard

Create a New Report

1. Open Power BI Desktop and connect to your prepared dataset.

2. Define Key Metrics: Use DAX to create measures for key metrics.

   Total Sales = SUM(Sales[Amount])
   Total Units Sold = SUM(Sales[Quantity])
   Average Sales Price = DIVIDE([Total Sales], [Total Units Sold], 0)

3. Create Charts and Visuals:

   • Total Sales Line Chart: Add a line chart visual to your report Axis: Date[Date] Values: [Total Sales]

   • Units Sold by Product Category: Add a bar chart visual to your report Axis: Product[Category] Values: [Total Units Sold]

   • Sales by Region: Add a map visual to your report Location: Sales[Region] Size: [Total Sales]

Inventory Management Dashboard

Interactive Inventory Insights

1. Define Inventory Measures:

   Total Inventory = SUM(Inventory[Stock])
   Inventory Days = [Total Inventory] / [Total Units Sold] * 30

2. Create Visuals:

   • Current Stock Levels: Add a column chart visual to show stock levels by product category Axis: Product[Category] Values: [Total Inventory]

   • Inventory Over Time: Add a line chart visual to track inventory over months Axis: Date[Month] Values: [Total Inventory]

Customer Behavior Dashboard

Analyzing Customer Trends

1. Define Customer Measures:

   Total Customers = DISTINCTCOUNT(Sales[CustomerID])
   Repeat Customers = CALCULATE([Total Customers], FILTER(Sales, Sales[OrderCount] > 1))
   New Customers = CALCULATE([Total Customers], FILTER(Sales, Sales[OrderCount] = 1))

2. Create Visuals:

   • Customer Growth: Add a line chart visual to show new vs repeat customers over time Axis: Date[Month] Values: [New Customers], [Repeat Customers]

   • Customer Demographics: Add a pie chart visual to show customer distribution by age group or region Details: Customer[AgeGroup] or Customer[Region] Values: [Total Customers]

Supply Chain Efficiency Dashboard

Optimize Supply Chain Operations

1. Define Supply Chain Measures:

   Average Delivery Time = AVERAGE(SupplyChain[DeliveryDays])
   On-Time Deliveries = CALCULATE(COUNT(SupplyChain[DeliveryID]), SupplyChain[Status] = "On-Time")

2. Create Visuals:

   • Delivery Performance: Add a bar chart visual to show on-time delivery rates by supplier or product category Axis: SupplyChain[Supplier] or Product[Category] Values: [On-Time Deliveries]

   • Average Delivery Time: Add a line chart visual to track delivery times over months Axis: Date[Month] Values: [Average Delivery Time]

Combine Dashboards into Comprehensive Report

1. Create Report Pages:

   • Separate each of the above dashboards into dedicated pages for ease of navigation.
   • Use consistent formatting and color schemes to create a cohesive report.

2. Add Interactive Elements:

   • Add filters and slicers for date ranges, product categories, regions, etc.
   • Ensure visuals update dynamically based on selected filters.

3. Review and Publish:

   • Finalize the layout and formatting for readability.
   • Publish the report to your Power BI service for access and sharing.

This practical implementation provides a clear and actionable approach to creating comprehensive dashboards in Power BI using DAX.