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
- Total Sales Calculation
Total Sales = SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])
- Explanation: Calculates total revenue by multiplying quantity by unit price for each row.
- Total Quantity Sold
Total Quantity = SUM(OnlineRetail[Quantity])
- Unique Customers Count
Unique Customers = DISTINCTCOUNT(OnlineRetail[CustomerID])
- Sales by Country
Sales by Country = SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])
Intermediate DAX Queries
- Sales for a Specific Product
Sales for Product A = CALCULATE( SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice]), OnlineRetail[Description] = "Product A" )
- Average Sales Per Customer
Avg Sales Per Customer = AVERAGEX( VALUES(OnlineRetail[CustomerID]), CALCULATE(SUMX(OnlineRetail, OnlineRetail[Quantity] * OnlineRetail[UnitPrice])) )
- 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
- Year-over-Year Growth
YoY Growth = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR(OnlineRetail[InvoiceDate]) )
- Cumulative Sales
Cumulative Sales = CALCULATE( [Total Sales], FILTER( ALL(OnlineRetail[InvoiceDate]), OnlineRetail[InvoiceDate] <= MAX(OnlineRetail[InvoiceDate]) ) )
- Customer Segmentation (High Value Customers)
High Value Customers = CALCULATE( [Total Sales], FILTER( VALUES(OnlineRetail[CustomerID]), [Total Sales] > 1000 ) )
- 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
- Import the dataset into Power BI.
- Use the
Data
view to create DAX measures. - 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?