Measures

What You'll Learn

• What measures are and why they're powerful
• Create measures with DAX
• Understanding context
• Common measure patterns

What are Measures?

Measures are dynamic DAX calculations evaluated at query time.

Key characteristics:

• Calculated on-the-fly
• Context-aware (respond to filters)
• Don't take up storage
• Cannot be used in slicers
• The heart of Power BI analytics!

Measures vs Calculated Columns

Measure: Total Sales = SUM(Sales[Amount]) Changes based on filters!

Calculated Column: RowTotal = [Quantity] * [Price] Fixed value per row

When to use measures:

• Aggregations (SUM, AVG, COUNT)
• Percentages, ratios
• Time intelligence
• Anything that changes with filters

Creating Measures

Method 1: Right-click table > New Measure

Method 2: Home > New Measure

Method 3: Modeling > New Measure

Formula appears in formula bar at top

Basic Aggregation Measures

SUM

Total Sales = SUM(Sales[Amount])

AVERAGE

Average Order = AVERAGE(Sales[Amount])

COUNT

Order Count = COUNT(Sales[OrderID])

COUNTROWS

Total Orders = COUNTROWS(Sales)

COUNT vs COUNTROWS:

• COUNT: Counts non-blank values in column
• COUNTROWS: Counts all rows in table

MIN / MAX

Lowest Sale = MIN(Sales[Amount]) Highest Sale = MAX(Sales[Amount])

DISTINCTCOUNT

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Counts unique values only!

Understanding Filter Context

Filter context is what makes measures powerful!

Example: Total Sales = SUM(Sales[Amount])

No filters: Shows total for entire dataset

With Product filter: Shows total for that product only

With Date filter: Shows total for that time period

Measures automatically respond to:

• Slicers
• Visual filters
• Cross-filtering from other visuals
• Report/page filters

Percentage Calculations

Percentage of Total

Sales % = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)) )

ALL() removes filters to get grand total

Percentage Change

% Change = DIVIDE( [Total Sales] - [Previous Sales], [Previous Sales] )

Margin Percentage

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

DIVIDE handles division by zero automatically!

Conditional Measures

SUMIF Pattern

High Value Sales = SUMX( FILTER(Sales, Sales[Amount] >= 1000), Sales[Amount] )

Count with condition

Large Orders = COUNTROWS(FILTER(Sales, Sales[Amount] >= 1000))

CALCULATE with filter

VIP Sales = CALCULATE( SUM(Sales[Amount]), Customers[Type] = "VIP" )

CALCULATE Function

Most important DAX function!

Purpose: Modify filter context

Syntax: CALCULATE(expression, filter1, filter2, ...)

Examples:

Remove filter: All Sales = CALCULATE([Total Sales], ALL(Products))

Add filter: USA Sales = CALCULATE([Total Sales], Customers[Country] = "USA")

Multiple filters: Premium USA = CALCULATE( [Total Sales], Products[Category] = "Premium", Customers[Country] = "USA" )

Ratio and Comparison Measures

Actual vs Target

Achievement % = DIVIDE([Actual Sales], [Target Sales])

Market Share

Market Share = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALL(Products)) )

Running Total

Running Total = CALCULATE( [Total Sales], FILTER( ALL(Calendar[Date]), Calendar[Date] <= MAX(Calendar[Date]) ) )

Common Measure Patterns

Safety with DIVIDE

Instead of: Ratio = [Numerator] / [Denominator]

Use: Ratio = DIVIDE([Numerator], [Denominator], 0)

Third parameter is what to return if denominator is zero

Blank instead of zero

Total Sales = VAR Sales = SUM(Sales[Amount]) RETURN IF(Sales = 0, BLANK(), Sales)

Shows blank instead of 0 in visuals

Variables

Use VAR for readability:

Profit Margin = VAR TotalRevenue = SUM(Sales[Revenue]) VAR TotalCost = SUM(Sales[Cost]) VAR Profit = TotalRevenue - TotalCost RETURN DIVIDE(Profit, TotalRevenue)

Benefits:

• Easier to read
• Better performance (calculates once)
• Easier to debug

Formatting Measures

Right-click measure > Format:

Currency:

• $#,##0
• €#,##0

Percentage:

• 0.00%
• 0%

Decimal:

• #,##0.00
• #,##0

Custom: Define your own format!

Organizing Measures

Create measure tables:

1. Home > Enter Data
2. Create blank table named "_Measures"
3. Delete default column
4. Move all measures here
5. Hide table from report view

Keeps measures organized!

Try This Exercise

Create these measures:

1. Basic aggregation: Total Revenue = SUM(Sales[Amount])

2. Average: Avg Order Value = AVERAGE(Sales[Amount])

3. Percentage: Margin % = DIVIDE([Total Revenue] - [Total Cost], [Total Revenue])

4. Conditional: Large Orders = COUNTROWS(FILTER(Sales, Sales[Amount] >= 1000))

5. Format each appropriately

6. Test in visuals:

   • Add to cards
   • Slice by different dimensions
   • Verify they respond to filters

Debugging Measures

Measure shows wrong number:

• Check filter context
• Verify relationships
• Test with simple data

Measure shows blank:

• Might be correct (no data for filter)
• Check for errors in formula

Performance issues:

• Avoid complex nested CALCULATE
• Use variables
• Consider calculated column for static calcs

Next Steps

Now let's learn Basic DAX Functions to expand your measure toolkit!

Tip: Mastering measures = mastering Power BI. Spend time here!