Basic DAX Measures for Custom Calculations and KPIs

1. What is DAX? Measures vs. Calculated Columns

Think of DAX as a super-powered version of Excel formulas. While many functions will feel familiar (SUM, AVERAGE, IF), DAX is designed to work with entire tables and columns of data within the relational model you built in the last lesson.

Before writing any DAX, you must understand the two primary ways DAX calculations are stored: Calculated Columns and Measures. Getting this distinction right from the start will save you countless hours of confusion.

• Calculated Columns: A calculated column creates a new column in one of your tables. The formula is evaluated for each row in the table during data refresh and the results are stored in your model.

  • When to use: For static, row-level logic. For example, if you have a Sales table with Revenue and Cost columns, you could create a calculated column Gross Profit Amount = Sales[Revenue] - Sales[Cost]. This calculates the profit for each individual transaction. It's useful for categorizing or segmenting data that you'll use later in filters or slicers.
  • Cost: They consume memory (RAM) because the results are stored.

• Measures: A measure is a formula for an aggregation. It doesn't store any values in your model. Instead, it is calculated on-the-fly whenever you add it to a visual in your report. The calculation responds dynamically to the context of the visual (e.g., filters, slicers, rows, and columns).

  • When to use: For aggregated results and KPIs. For example, a measure Total Revenue = SUM(Sales[Revenue]) calculates the sum of revenue for whatever context is active—total company, a specific year, a single product, etc. This is the primary tool for FP&A.
  • Cost: They consume processing power (CPU) at query time.

For your FP&A work, you will be creating measures about 95% of the time.

2. Your First Measures: Simple Aggregations and Arithmetic

Let's start by creating some simple measures. The syntax is straightforward: Measure Name = FUNCTION(TableName[ColumnName]). In Power BI, you can create a new measure by right-clicking on any table in the 'Data' pane and selecting 'New measure'. A best practice is to create a dedicated, empty table to hold all your measures to keep your model organized.

The most common DAX functions are simple aggregations you already know from Excel:

• SUM(): Adds up all the numbers in a column.
• AVERAGE(): Calculates the arithmetic mean.
• COUNT(): Counts the number of rows in a column.
• DISTINCTCOUNT(): Counts the number of unique values in a column.

Once you have a few base measures, you can combine them using standard arithmetic operators (+, -, *, /) to create KPIs. This is a powerful feature: measures can refer to other measures.

// First, create base measures for revenue and cost
Total Revenue = SUM(Financials[Amount])
Total COGS = SUM(Financials[Cost])

// Now, create a KPI by referencing the other measures
Gross Profit = [Total Revenue] - [Total COGS]

Notice how [Gross Profit] is more readable and modular than writing SUM(Financials[Amount]) - SUM(Financials[Cost]). This "measure branching" is fundamental to building complex yet maintainable models.

3. The Superpower of DAX: The CALCULATE Function

If DAX had one function that defined its power, it would be CALCULATE. It is the key to unlocking sophisticated analysis.

CALCULATE changes the filter context for a calculation.

Recall that measures are evaluated within a "filter context"—the set of filters coming from your visual (e.g., a specific year on a chart axis) and slicers. CALCULATE allows you to override or add to that context.

The basic syntax is: CALCULATE(<expression>, <filter1>, <filter2>, ...)

Let's see a practical FP&A example. Imagine your general ledger data is in one large table called pnl_daily_2025, and you have a dimension table DimPL_Account that categorizes each transaction (e.g., "Revenue", "COGS", "Opex").

You can create a base measure for the total amount:
PL Amount = SUM(pnl_daily_2025[Amount])

This measure isn't very useful on its own. But using CALCULATE, we can create specific P&L line items:
Revenue = CALCULATE( [PL Amount], DimPL_Account[Account] = "Revenue" )
COGS = CALCULATE( [PL Amount], DimPL_Account[Account] = "COGS" )

Here, CALCULATE takes our base [PL Amount] measure and applies an additional filter: calculate the sum of Amount only for rows where the account is "Revenue".

4. Writing Cleaner Code with Variables

As your formulas become more complex, they can become difficult to read and debug. DAX provides the VAR...RETURN syntax to structure your calculations logically.

Using variables allows you to:

• Improve Readability: Break a complex calculation into named, logical steps.
• Boost Performance: If you need to use a calculated value multiple times within the same measure, storing it in a variable ensures it is computed only once.
• Simplify Debugging: You can temporarily change your RETURN statement to output the value of any intermediate variable to check your logic.

Let's look at the Headcount (EOP) measure from the resource you just read:

Headcount (EOP) =
VAR _eop = MAX('Calendar'[Date])
RETURN
    CALCULATE(
        DISTINCTCOUNT(payroll_roster_2025[EmployeeID]),
        REMOVEFILTERS('Calendar'),
        payroll_roster_2025[HireDate] <= _eop,
        OR(
            ISBLANK(payroll_roster_2025[TerminationDate]),
            payroll_roster_2025[TerminationDate] > _eop
        )
    )

Breaking it down:

1. VAR _eop = MAX('Calendar'[Date]): First, we create a variable _eop (end of period) that stores the latest date in the current filter context.
2. RETURN CALCULATE(...): Then, we return the result of our main calculation.
3. DISTINCTCOUNT(...): We count the unique employee IDs...
4. REMOVEFILTERS('Calendar'): ...but first, we remove any existing filters on the Calendar table to ensure our date logic works correctly.
5. payroll_roster_2025[HireDate] <= _eop: ...and apply a new filter to only include employees hired on or before the end of the period.
6. OR(...): ...and they must either have no termination date or a termination date after the end of the period.

This is an advanced example, but it perfectly illustrates how variables make complex business logic manageable.

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

Conclusion

You have now taken your first and most important steps with DAX. You've learned the critical difference between measures and calculated columns, how to write basic aggregation formulas, and how to use the CALCULATE function to manipulate filter context—the core engine of DAX.

Key Takeaways:

• DAX is the formula language for Power BI; think of it as Excel formulas on steroids.
• For FP&A, use Measures for all your aggregated KPIs (Revenue, EBITDA, etc.) and use Calculated Columns sparingly for static, row-level data.
• The CALCULATE function is your most powerful tool. It modifies the filter context to let you define highly specific calculations.
• Use the VAR...RETURN syntax to write clean, efficient, and easy-to-debug measures.
• Build your analysis by composing measures: create simple base measures, then reference them to build more complex KPIs.

Preview of the Next Lesson:
We have built the model's structure and learned how to write the calculations. In our next lesson, Build an interactive FP&A dashboard in Power BI, we will bring it all together. You'll use the DAX measures you've learned to create compelling visuals like waterfall charts, variance analyses, and KPI cards, transforming your data model into a powerful tool for business stakeholders.