DAX Measures: 20 Practical Examples
DAX (Data Analysis Expressions) is a powerful formula language used in Power BI, Excel, and SQL Server Analysis Services. It allows users to create custom calculations and aggregations in their data models. In this article, we will explore 20 practical examples of measures, demonstrating how to create dynamic and insightful calculations in your reports.
Example 1: Total Sales
Creating the Sales Table
SalesTable =
ADDCOLUMNS(
GENERATESERIES(1, 100, 1),
"Product", "Product " & [Value],
"SalesAmount", 100 + RAND() * 900
)
Creating the Measure
TotalSales = SUM(SalesTable[SalesAmount])
Example 2: Average Sales
Creating the Measure
AverageSales = AVERAGE(SalesTable[SalesAmount])
Example 3: Count of Products Sold
Creating the Measure
CountOfProductsSold = COUNTROWS(SalesTable)
Example 4: Maximum Sales Amount
Creating the Measure
MaxSalesAmount = MAX(SalesTable[SalesAmount])
Example 5: Minimum Sales Amount
Creating the Measure
MinSalesAmount = MIN(SalesTable[SalesAmount])
Example 6: Total Sales with Discount
Creating the Measure
TotalSalesWithDiscount =
VAR DiscountRate = 0.10
RETURN
SUMX(SalesTable, SalesTable[SalesAmount] * (1 - DiscountRate))
Example 7: Year-to-Date Sales
Creating the Measure
YTD_Sales = TOTALYTD(SUM(SalesTable[SalesAmount]), 'Date'[Date])
Example 8: Sales Growth Percentage
Creating the Measure
SalesGrowthPercentage =
VAR CurrentYearSales = CALCULATE(SUM(SalesTable[SalesAmount]), 'Date'[Year] = YEAR(TODAY()))
VAR PreviousYearSales = CALCULATE(SUM(SalesTable[SalesAmount]), 'Date'[Year] = YEAR(TODAY()) - 1)
RETURN
DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales, 0)
Example 9: Sales by Category
Creating the Measure
SalesByCategory =
CALCULATE(SUM(SalesTable[SalesAmount]), SalesTable[Category] = "Electronics")
Example 10: Total Quantity Sold
Creating the Quantity Table
QuantityTable =
ADDCOLUMNS(
GENERATESERIES(1, 100, 1),
"Product", "Product " & [Value],
"QuantitySold", ROUND(RAND() * 100, 0)
)
Creating the Measure
TotalQuantitySold = SUM(QuantityTable[QuantitySold])
Example 11: Average Quantity Sold
Creating the Measure
AverageQuantitySold = AVERAGE(QuantityTable[QuantitySold])
Example 12: Count of Unique Products
Creating the Measure
CountOfUniqueProducts = DISTINCTCOUNT(SalesTable[Product])
Example 13: Sales Per Category
Creating the Measure
SalesPerCategory =
SUMX(
VALUES(SalesTable[Category]),
CALCULATE(SUM(SalesTable[SalesAmount]))
)
Example 14: Total Revenue
Creating the Measure
TotalRevenue = SUMX(SalesTable, SalesTable[SalesAmount] * 1.2) // Assuming 20% markup
Example 15: Conditional Sales
Creating the Measure
ConditionalSales =
SUMX(
SalesTable,
IF(SalesTable[SalesAmount] > 500, SalesTable[SalesAmount], 0)
)
Example 16: Running Total of Sales
Creating the Measure
RunningTotalSales =
CALCULATE(
SUM(SalesTable[SalesAmount]),
FILTER(
ALLSELECTED('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
)
)
Example 17: Average Sales Over Time
Creating the Measure
AverageSalesOverTime =
AVERAGEX(
VALUES('Date'[Date]),
CALCULATE(SUM(SalesTable[SalesAmount]))
)
Example 18: Sales Contribution to Total
Creating the Measure
SalesContribution =
DIVIDE(SUM(SalesTable[SalesAmount]), [TotalSales], 0)
Example 19: Total Expenses
Creating the Expenses Table
ExpensesTable =
ADDCOLUMNS(
GENERATESERIES(1, 100, 1),
"ExpenseType", "Expense " & [Value],
"ExpenseAmount", 50 + RAND() * 450
)
Creating the Measure
TotalExpenses = SUM(ExpensesTable[ExpenseAmount])
Example 20: Profit Calculation
Creating the Measure
Profit = [TotalSales] - [TotalExpenses]
This article provides a comprehensive overview of 20 practical DAX measures that can be used in your Power BI reports. Each example illustrates a different aspect of data analysis, helping you leverage DAX to gain deeper insights into your data.
edvaldo b. guimarães filho 
Leave a comment