Sign In
Get Started

Comprehensive Data Analysis using Power BI and DAX

by | Power BI

Table of Contents

Power BI Data Import and Model Setup

1. Import Data

Open Power BI Desktop.
Click on Home -> Get Data.
Select the CSV file containing your order data (or select Excel, SQL Server, etc. depending on your data source).
Load the data.

2. Inspect and Transform Data

Click on Transform Data:
Inspect columns and data types.
Remove any unnecessary columns.
Rename columns appropriately.
Adjust data types if necessary.

Example of data transforms:

let
    Source = Csv.Document(File.Contents("C:\Orders.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    RemovedColumns = Table.RemoveColumns(PromotedHeaders,{"UnnecessaryColumn1","UnnecessaryColumn2"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"OldName1", "NewName1"}, {"OldName2", "NewName2"}}),
    ChangedTypes = Table.TransformColumnTypes(RenamedColumns,{{"OrderDate", type date}, {"OrderAmount", type number}})
in
    ChangedTypes

3. Setup Data Model

In Power BI Desktop, go to Model view.
Define relationships between different tables if you have multiple datasets.
Drag and drop fields to create relationships.
Ensure cardinality (one-to-many, many-to-one) is correct.
Configure relationship properties (e.g., cross-filter direction).

Example relationships:

Sales Now On Advertisement
Orders Table: OrderID
Customers Table: CustomerID
Products Table: ProductID

4. Initial Calculations with DAX

Create basic measures for analysis:
Click on New Measure in the modeling tab.

Example DAX measures:

Total Sales = SUM(Orders[OrderAmount])

Total Orders = COUNTROWS(Orders)
  1. Create calculated columns if needed:

    Click on New Column.


Example DAX calculated columns:

OrderYear = YEAR(Orders[OrderDate])

OrderMonth = FORMAT(Orders[OrderDate], "MMMM")

5. Save and Publish

Click on File -> Save As to save your Power BI project.
Publish to Power BI Service:
Click on Home -> Publish.
Sign in and select the appropriate workspace.

This setup enables you to import data into Power BI, transform it as needed, establish relationships, and create initial measures to begin your analysis. Next steps will involve more advanced DAX formulas and visualization targets.

Power BI: Table Relationships and Data Cleansing

Step 1: Table Relationships

Create Relationships

Open the Model view in Power BI.
Drag the CustomerID field from the Orders table to the CustomerID field in the Customers table to create a relationship.
Repeat for ProductID in the Orders table and ProductID in the Products table.

Ensure the relationships are:

Orders[CustomerID] -> Customers[CustomerID]
Orders[ProductID] -> Products[ProductID]

Step 2: Data Cleansing

Remove Duplicates

CleansedOrders = DISTINCT(Orders)

Handle Missing Values

Replace missing values in the OrderAmount field with 0:

Orders = 
    ADDCOLUMNS(
        Orders,
        "CleanedOrderAmount",
        IF(ISBLANK(Orders[OrderAmount]), 0, Orders[OrderAmount])
    )

Filter Out Outliers

For numerical data like OrderAmount, filter out values that are outside 3 standard deviations:

MeanOrderAmount = AVERAGEX(Orders, Orders[OrderAmount])
StdDevOrderAmount = STDEVX.P(Orders, Orders[OrderAmount])

CleansedOrders = 
    FILTER(
        Orders,
        Orders[OrderAmount] >= MeanOrderAmount - 3 * StdDevOrderAmount &&
        Orders[OrderAmount] <= MeanOrderAmount + 3 * StdDevOrderAmount
    )

Remove Invalid Entries

Filter out orders with invalid OrderDate entries:

Power BI Tools Advertisement 
CleansedOrders = 
    FILTER(
        Orders,
        NOT(ISBLANK(Orders[OrderDate])) &&
        Orders[OrderDate] > DATE(2000, 1, 1)
    )

Summary

Relationships: Established between tables using corresponding IDs.
Data Cleansing:
Removed duplicates.
Replaced missing values.
Filtered outliers based on standard deviations.
Removed invalid OrderDate entries.

Your Power BI data model is now relationally correct and the data within it is cleaned, ready for analysis and DAX formula application.

Basic DAX Measures Implementation for Power BI

Measures

1. Total Sales

Calculates the total sales amount.

Total Sales = SUM('Orders'[SalesAmount])

2. Total Quantity

Calculates the total quantity of products sold.

Total Quantity = SUM('Orders'[Quantity])

3. Average Sales

Calculates the average sales amount per order.

Average Sales = AVERAGE('Orders'[SalesAmount])

4. Order Count

Counts the number of orders.

Order Count = COUNTROWS('Orders')

5. Sales Per Order

Calculates sales per order.

Sales Per Order = [Total Sales] / [Order Count]

6. Sales Growth YOY

Calculates year-over-year sales growth.

Sales Growth YOY = 
    CALCULATE(
        [Total Sales],
        DATEADD('Orders'[OrderDate], -1, YEAR)
    )

7. Sales Difference YOY

Calculates the difference in sales compared to last year.

Sales Difference YOY = [Total Sales] - [Sales Growth YOY]

8. Sales Percentage Growth YOY

Calculates the percentage growth in sales compared to last year.

Sales Percentage Growth YOY = 
    DIVIDE(
        [Sales Difference YOY],
        [Sales Growth YOY],
        0
    )

9. Gross Profit

Calculates the gross profit from sales.

Gross Profit = SUMX(
    'Orders',
    'Orders'[SalesAmount] - 'Orders'[CostAmount]
)

10. Profit Margin

Calculates the profit margin.

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

These measures can be created within the ‘Orders’ table in Power BI’s data model. Ensure the data is clean and relationships are established correctly for an accurate analysis.

Advanced DAX Calculations for Power BI

Part #4: Advanced DAX Calculations

1. Year-Over-Year Sales Growth

YoY Sales Growth = 
VAR CurrentYearSales = 
    CALCULATE(
        SUM('Orders'[Sales]),
        YEAR('Orders'[OrderDate]) = YEAR(TODAY())
    )
VAR LastYearSales = 
    CALCULATE(
        SUM('Orders'[Sales]),
        YEAR('Orders'[OrderDate]) = YEAR(TODAY()) - 1
    )
RETURN
    DIVIDE(CurrentYearSales - LastYearSales, LastYearSales, 0)

2. Moving Average Sales (6 Months)

MovingAvgSales6M = 
CALCULATE(
    AVERAGEX(
        DATESINPERIOD(
            'Calendar'[Date],
            LASTDATE('Orders'[OrderDate]),
            -6,
            MONTH
        ),
        CALCULATE(SUM('Orders'[Sales]))
    )
)

3. Customer Lifetime Value (CLV)

Customer Lifetime Value = 
CALCULATE(
    SUMX(
        VALUES('Customers'[CustomerID]),
        SUM('Orders'[Sales])
    ),
    ALLEXCEPT('Orders', 'Customers'[CustomerID])
)

4. Average Order Value (AOV)

Average Order Value = 
DIVIDE(
    SUM('Orders'[Sales]),
    COUNT('Orders'[OrderID]),
    0
)

5. Sales Variance

Sales Variance = 
VAR CurrentPeriodSales = SUM('Orders'[Sales])
VAR PreviousPeriodSales = 
    CALCULATE(
        SUM('Orders'[Sales]),
        DATEADD('Orders'[OrderDate], -1, MONTH)
    )
RETURN
    CurrentPeriodSales - PreviousPeriodSales

6. Basket Size

Basket Size = 
DIVIDE(
    COUNT('OrderDetails'[ProductID]),
    COUNTROWS(SUMMARIZE('OrderDetails', 'Orders'[OrderID]))
)

7. Sales Contribution by Product

Sales Contribution by Product = 
DIVIDE(
    SUM('OrderDetails'[Sales]),
    CALCULATE(SUM('OrderDetails'[Sales]), ALLEXCEPT('Products', 'Products'[ProductID])),
    0
)

8. Customer Retention Rate

Customer Retention Rate = 
VAR PreviousOrders = 
    CALCULATE(
        DISTINCTCOUNT('Orders'[CustomerID]),
        DATEADD('Orders'[OrderDate], -1, YEAR)
    )
VAR CurrentOrders = DISTINCTCOUNT('Orders'[CustomerID])
RETURN
    DIVIDE(CurrentOrders, PreviousOrders, 0)

Summary

These DAX formulas assess various metrics including Year-Over-Year Sales Growth, Moving Average Sales, Customer Lifetime Value, Average Order Value, Sales Variance, Basket Size, Sales Contribution by Product, and Customer Retention Rate. Use these calculations directly in your Power BI model to derive actionable insights.

Time Intelligence with DAX

Measures for Time Intelligence Analysis

Creating a Date Table

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2010, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1,
    "Day", DAY([Date]),
    "WeekdayName", FORMAT([Date], "dddd"),
    "IsWorkingDay", IF(WEEKDAY([Date], 2) <= 5, TRUE, FALSE),
    "YearMonth", FORMAT([Date], "YYYYMM")
)

Year-to-Date (YTD) Revenue

YTDRevenue = 
CALCULATE(
    SUM('Sales'[Revenue]),
    DATESYTD('DateTable'[Date])
)

Quarter-to-Date (QTD) Revenue

QTDRevenue = 
CALCULATE(
    SUM('Sales'[Revenue]),
    DATESQTD('DateTable'[Date])
)

Month-to-Date (MTD) Revenue

MTDRevenue = 
CALCULATE(
    SUM('Sales'[Revenue]),
    DATESMTD('DateTable'[Date])
)

Revenue Same Period Last Year (SPLY)

SPLYRevenue = 
CALCULATE(
    SUM('Sales'[Revenue]),
    SAMEPERIODLASTYEAR('DateTable'[Date])
)

Adding Time Intelligence Filters

Filter: Current Year

CurrentYearRevenue = 
CALCULATE(
    [TotalRevenue],
    FILTER(
        'DateTable',
        'DateTable'[Year] = YEAR(TODAY())
    )
)

Filter: Previous Month

PreviousMonthRevenue = 
CALCULATE(
    [TotalRevenue],
    PREVIOUSMONTH('DateTable'[Date])
)

Rolling Averages

3-Month Rolling Average Revenue

3MonthRollingAvgRevenue = 
AVERAGEX(
    DATESINPERIOD(
        'DateTable'[Date], 
        LASTDATE('DateTable'[Date]), 
        -3, 
        MONTH
    ),
    [TotalRevenue]
)

12-Month Rolling Average Revenue

12MonthRollingAvgRevenue = 
AVERAGEX(
    DATESINPERIOD(
        'DateTable'[Date], 
        LASTDATE('DateTable'[Date]), 
        -12, 
        MONTH
    ),
    [TotalRevenue]
)

Custom Measures

Year-over-Year Growth

YoYGrowth = 
DIVIDE(
    [CurrentYearRevenue] - [SPLYRevenue],
    [SPLYRevenue],
    0
)

Month-over-Month Growth

MoMGrowth = 
DIVIDE(
    [CurrentMonthRevenue] - [PreviousMonthRevenue],
    [PreviousMonthRevenue],
    0
)

Implementation in Power BI

Add the date table to your data model.
Use the DAX formulas provided to create measures.
Drag and drop these measures into your reports to visualize revenue trends and perform time-based analysis.

All DAX calculations are encapsulated as measures to be directly used in your Power BI charts and tables.

Visualization and Dashboard Creation in Power BI

Step-by-step Instructions

Load Your Data\nEnsure your data models and measures are ready in Power BI.

Data Mentor Advertisement

Create a New Report

Open Power BI Desktop.
Click on File > New to create a new report.

Add Visualizations\nDrag and drop the desired fields to the canvas from the fields pane to create visualizations.

Sales Summary Card:

// Total Sales Measure
Total Sales = SUM('Sales'[SalesAmount])

Select Card visualization, and drag the Total Sales measure to it.

Sales by Category Chart:
Drag Category to the Axis and Total Sales to the Values pane. Use the Bar Chart visualization.

Monthly Sales Trend:

// Sales by Month Measure
Sales by Month = 
  CALCULATE(
    [Total Sales],
    DATESMTD('Date'[Date])
  )

Drag Date to the Axis and Sales by Month to the Values pane. Use the Line Chart visualization.

Top 5 Customers by Sales:

// Top 5 Customers Sales Measure
Top 5 Customers Sales = 
  TOPN(5, SUMMARIZE('Sales', 'Customer'[CustomerName], "Total Customer Sales", [Total Sales]), [Total Customer Sales], DESC)

Drag CustomerName to the Axis and Total Customer Sales to the Values pane. Use the Bar Chart visualization.

Adding Filters and Slicers

Add a Slicer for Date: Drag Date onto the report canvas and select the Slicer visualization.
Add a filter for Categories: Drag Category onto the Filters pane to apply it to the whole report or specific visuals.

Design and Arrange

Adjust the visual sizes and positions to ensure a clean layout.
Use Align and Distribute features from the Format tab to organize the visualizations neatly.

Enhancing with Drill Through

Add pages for detailed views.
Enable Drill Through on specific charts for categories or customers by right-clicking on the respective visualization and selecting Drill Through.

Final Touches

Add titles and descriptions to each visualization.
Customize colors and themes: From View > Themes > Customize current theme.

Publish the Report

Save your report.
Click Publish from the Home tab to publish to Power BI Service.

Example Visuals Layout

First Row:

Sales Summary (Card)
Sales by Category (Bar Chart)

Second Row:

Monthly Sales Trend (Line Chart)
Top 5 Customers by Sales (Bar Chart)

Filters/Slicers:

Left or right pane with Date slicer and Category filter.

Power BI Service Dashboard Creation

Go to Power BI Service

Pin Visuals to Dashboard

Open the published report.
Hover over each visual and click the Pin icon.
Pin to a new Dashboard or existing one.

  • Arrange Dashboard Tiles

    Drag and drop to adjust the size and position of each pinned visual.

Set Alerts (Optional)

EDNA AI Advertisement
Set data alerts for key metrics if needed by clicking on the ellipsis button on a tile and selecting Manage Alerts.

This sequence will help create a functional and insightful dashboard in Power BI.

Performance Analysis and Insights in Power BI using DAX

Step 1: Create Key Performance Indicators (KPIs)

Total Sales = SUM(Orders[OrderAmount])

Total Orders = COUNTROWS(Orders)

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

Sales Growth = DIVIDE(
    [Total Sales] - CALCULATE(
        [Total Sales],
        DATEADD(Orders[OrderDate], -1, YEAR)
    ),
    CALCULATE(
        [Total Sales],
        DATEADD(Orders[OrderDate], -1, YEAR)
    )
)

Step 2: Customer Behavior Analysis

Customer Lifetime Value (CLV) = CALCULATE(
    SUM(Orders[OrderAmount]),
    ALLEXCEPT(Customers, Customers[CustomerID])
)

Average Customer Lifetime Value = AVERAGEX(
    VALUES(Customers[CustomerID]),
    [Customer Lifetime Value (CLV)]
)

Customer Retention Rate = DIVIDE(
    COUNTROWS(
        FILTER(
            Customers,
            CALCULATE(
                COUNTROWS(Orders),
                Orders[OrderDate] > TODAY() - 365
            ) > 0
        )
    ),
    COUNTROWS(Customers)
)

Step 3: Product Performance Analysis

Top Selling Products = TOPN(
    10,
    SUMMARIZE(
        Products,
        Products[ProductID],
        "Total Sales", [Total Sales]
    ),
    [Total Sales],
    DESC
)

Product Sales Contribution = DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALL(Products)
    ),
    0
)

Product Return Rate = DIVIDE(
    [Total Returns],
    [Total Sales]
)

Step 4: Visualize the Insights

  1. Create visualizations for each KPI (bar charts for Total Sales, Total Orders; line charts for Sales Growth, etc.).
  2. Use column charts for the Top Selling Products.
  3. Use pie charts or donut charts to show Product Sales Contribution.
  4. Use a line or bar chart for Customer Retention Rate over time.

Step 5: Adding Insights to Dashboard

  • Add a card visual for each KPI to display the aggregated values.
  • Create bar charts for Top Selling Products and Product Return Rate.
  • Use slicers and filters to allow dynamic interaction.

Step 6: Summary and Actionable Insights

  1. Add a text box summarizing insights:
    • Highlight areas of growth and decline.
    • Identify top-performing products and underperforming areas.
    • Suggest potential actions based on customer retention and CLV.

By following these steps, you’ll incorporate effective performance analysis and actionable insights directly into your Power BI dashboard using DAX.

Final Review and Optimization in Power BI (Using DAX)

Step 1: Verify Data Integrity

Ensure all data is loaded and accurate:

-- Verify row counts
Row_Count_Table1 = COUNTROWS(Table1)
Row_Count_Table2 = COUNTROWS(Table2)

-- Check for nulls in critical columns
Null_Check_Column1 = COUNTROWS(FILTER(Table1, ISBLANK(Table1[Column1])))
Null_Check_Column2 = COUNTROWS(FILTER(Table2, ISBLANK(Table2[Column2])))

Step 2: Optimize Measures

Review and optimize DAX measures for performance:

-- Example: Optimize a SUMX measure to use SUM
Total_Orders = SUM(OrderData[OrderAmount])

-- Replace complex conditions with variables
Sales_Target = 
VAR Sales = SUM(SalesData[SalesAmount])
RETURN
IF(Sales > 100000, "Target Achieved", "Target Not Achieved")

Step 3: Review Calculated Columns and Tables

Ensure calculated columns and tables are necessary. If possible, shift calculations to measures:

-- Convert calculated column to measure if suitable
Customer_Lifetime_Value_Measure = 
CALCULATE(
    SUM(OrderData[OrderAmount]),
    ALLEXCEPT(CustomerData, CustomerData[CustomerID])
)

Step 4: Clean Up Unused Fields

Remove or hide redundant columns and unused fields:

-- Hide columns not needed in the report

Right-click on columns and select ‘Hide in Report View’.

Step 5: Optimize Relationships

Ensure relationships are optimal and utilize correct cardinality:

-- Verify relationships are one-to-many or many-to-one as required

Go to Model view, check and adjust relationships if necessary.

Step 6: Index Columns for Performance (If applicable)

Index heavily queried columns:

-- Create indexes on primary keys and frequently filtered columns

Go to the data source and add indexes if supported.

Step 7: Use Aggregations (If applicable)

Implement aggregations for large datasets:

-- Create aggregate tables

Create summarized tables and set up aggregations in Power BI.

Step 8: Optimize Visuals

Reduce the number of visuals and optimize loaded data in visuals:

-- Use slicers efficiently and reduce visuals using high row data

Review and optimize report page layouts for performance.

Step 9: Review and Document Measures

Ensure all measures are documented and standardized:

-- Add comments to complex measures
/*
Measure to calculate growth rate
*/
Growth_Rate = 
(DIVIDE([CurrentYearRevenue], [PreviousYearRevenue], 0) - 1) * 100

Step 10: Final Validation

Validate key metrics and performance:

-- Cross-verify critical metrics
Check_TotalOrders = SUM(OrderData[OrderAmount])
Check_Margin = SUMX(OrderData, OrderData[OrderAmount] - OrderData[CostAmount])

Deployment:

Publish the finalized report to Power BI Service for end-user access.


> Save and publish the Power BI report using the provided 'Publish' feature in Power BI Desktop.

Related Posts

Exploring CALCULATETABLE Function in DAX for Data Analysis in Power BI

Data Model Discovery Library

An interactive web-based application to explore and understand various data model examples across multiple industries and business functions.