Power BI: Essential DAX Exercises for Business Reporting

In this tutorial, you’ll build four essential types of DAX expressions:

1. Measure – A reusable calculation (e.g., Profit and Margin).

2. Calculated Column – A new column added to a table (e.g., Price Band).

3. Calculated Table – A table built from DAX logic (e.g., Top 10 Products).

4. Time-Intelligence Measure – Period-based analysis (e.g., YTD, MTD, YoY).

Each exercise is designed around the fields and relationships already present in your dataset, making it practical and ready to implement in Power BI.

Download https://archive.org/download/powerbi_dataset/powerbi_ui_example.xlsx

1. Creating DAX Measures for Key Performance Indicators

Objective

To calculate core business KPIs: Total Revenue, Total Cost, Profit, and Profit Margin, using the Sales and Products tables.

Steps

1. Load powerbi_ui_example.xlsx into Power BI.

2. Ensure the relationship:

   • Sales[ProductID] → Products[ProductID]

3. Create a new Measure Table (Model View → New Table):

    Measures = { ("Placeholder") }
   

   Then delete the placeholder column (this is just for organizing measures).

DAX Code

Total Revenue :=
SUM ( Sales[Revenue] )

Total Quantity :=
SUM ( Sales[Quantity] )

Total Cost :=
SUMX (
    Sales,
    Sales[Quantity] * RELATED ( Products[Cost] )
)

Profit :=
[Total Revenue] - [Total Cost]

Profit Margin % :=
DIVIDE ( [Profit], [Total Revenue] )

How to Test

• Create Cards for [Total Revenue], [Profit], and [Profit Margin %].

• Create a Matrix with Country[Country] and Products[Category] on rows and the three measures as values.

• Confirm Profit = Revenue − (Quantity × Cost).

Business Impact: These KPIs show which regions or categories contribute most to profitability.

2. Creating a Calculated Table – Top 10 Products by Revenue

Objective

To create a summary table showing the Top 10 performing products based on total revenue.

Steps

1. In the Model view, select New Table.

2. Enter the following DAX code:

DAX Code

Top 10 Products by Revenue :=
VAR SummaryTable =
    SUMMARIZE (
        Sales,
        Products[Product],
        "Total Revenue", [Total Revenue],
        "Total Quantity", [Total Quantity]
    )
RETURN
TOPN ( 10, SummaryTable, [Total Revenue], DESC )

How to Test

• Create a Table visual and select columns from Top 10 Products by Revenue.

• Sort by Total Revenue descending.

• Compare against your main Sales data for validation.

Business Impact: This highlights your best-selling products for inventory, marketing, or pricing decisions.

3. Creating a Calculated Column – Product Price Band and Month-Year Label

Objective

To categorize products into Price Bands and create a Month-Year label for time-based visuals.

A. In the Products Table

Add a new Calculated Column:

Price Band :=
SWITCH (
    TRUE (),
    Products[Price] < 200, "Low",
    Products[Price] < 1000, "Medium",
    "High"
)

B. In the Date Table

Add a new Calculated Column:

Month-Year :=
FORMAT ( Date[Date], "YYYY-MMM" )

How to Test

• Create a Stacked Column Chart:

  • Axis → Products[Price Band]

  • Values → [Total Revenue]

• Add a Slicer using Date[Month-Year].

Business Impact: Simplifies reporting by allowing you to analyze sales across pricing segments and time periods.

4. Creating Time-Intelligence Measures – YTD, MTD, YoY

Objective

To create time-aware metrics that compare performance over periods (Year-to-Date, Month-to-Date, Year-over-Year).

Steps

1. Ensure your Date table is marked as a Date Table

   • Table Tools → Mark as Date Table → Select Date[Date]

2. Create the following DAX measures:

DAX Code

Revenue YTD :=
TOTALYTD ( [Total Revenue], Date[Date] )

Revenue MTD :=
TOTALMTD ( [Total Revenue], Date[Date] )

Revenue YoY :=
CALCULATE ( [Total Revenue], DATEADD ( Date[Date], -1, YEAR ) )

Revenue YoY % :=
DIVIDE ( [Total Revenue] - [Revenue YoY], [Revenue YoY] )

How to Test

• Add a Line Chart:

  • Axis → Date[Month-Year]

  • Values → [Total Revenue], [Revenue YTD], [Revenue YoY]

• Add a Card to show [Revenue YoY %].

Business Impact: Enables year-over-year growth tracking and cumulative sales trends for executive-level dashboards.

Summary of Skills Learned

.