Tags:

A commonly used dataset for analysis is the Online Retail Dataset or similar transactional datasets. Below is how you can use such a dataset for DAX practice, along with sample queries:


Dataset: Online Retail Dataset

Fields:

  • InvoiceNo (Unique identifier for each transaction)
  • StockCode (Unique product code)
  • Description (Product description)
  • Quantity (Number of units sold)
  • InvoiceDate (Date of purchase)
  • UnitPrice (Price per unit)
  • CustomerID (Unique customer identifier)
  • Country (Country of customer)

Dataset is available here : https://colorstech.net/wp-content/uploads/2025/01/Syn_Online_Retail_Data.xlsx


Basic DAX Queries

  1. Total Sales CalculationTotal Sales = SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])
    • Explanation: Calculates total revenue by multiplying quantity by unit price for each row.
  2. Total Quantity Sold Total Quantity = SUM(OnlineRetail[Quantity])
  3. Unique Customers Count Unique Customers = DISTINCTCOUNT(OnlineRetail[CustomerID])
  4. Sales by Country Sales by Country = SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])

Intermediate DAX Queries

  1. Sales for a Specific Product Sales for Product A = CALCULATE( SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice]), OnlineRetail[Description] = "Product A" )
  2. Average Sales Per Customer Avg Sales Per Customer = AVERAGEX( VALUES(OnlineRetail[CustomerID]), CALCULATE(SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])) )
  3. Top 5 Products by Sales Top Products = TOPN( 5, SUMMARIZE( OnlineRetail, OnlineRetail[Description], "Total Sales", SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice]) ), [Total Sales], DESC )

Advanced DAX Queries

  1. Year-over-Year Growth YoY Growth = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR(OnlineRetail[InvoiceDate]) )
  2. Cumulative Sales
  3. Cumulative Sales = CALCULATE( [Total Sales], FILTER( ALL(OnlineRetail[InvoiceDate]), OnlineRetail[InvoiceDate] <= MAX(OnlineRetail[InvoiceDate]) ) )
  4. Customer Segmentation (High Value Customers) High Value Customers = CALCULATE( [Total Sales], FILTER( VALUES(OnlineRetail[CustomerID]), [Total Sales] > 1000 ) )
  5. Sales Contribution by Country Sales Contribution = DIVIDE( SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice]), CALCULATE(SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])), 0 )

How to Use in Power BI

  1. Import the dataset into Power BI.
  2. Use the Data view to create DAX measures.
  3. Visualize:
    • Use bar charts for country-wise or product-wise sales.
    • Use line charts for cumulative sales or YoY growth.
    • Use slicers for filtering by product, country, or date.

Would you like help with preparing this dataset or further guidance on implementing these queries in Power BI?