DAX for Financial Calculations: Columns & Measures

Hello! Welcome to the next lesson on building interactive financial visuals in Power BI.

In our last session, we focused on making your reports dynamic by adding slicers and filters. You learned how to give users control to explore the data, for example, by filtering a portfolio dashboard to a specific time period or asset class. Now that you can slice and dice your data, the next logical step is to perform custom calculations on those filtered datasets.

Today, we dive into the world of DAX (Data Analysis Expressions). This is Power BI's formula language, and it's the key to unlocking deeper analytical insights. Our goal for this lesson is to learn how to use DAX to create simple calculated columns and measures for financial analysis, such as Year-over-Year growth. By the end, you'll be able to move beyond the default aggregations (like sum or average) and start creating your own meaningful financial metrics.

1. What is DAX? Calculated Columns vs. Measures

At first glance, DAX formulas might remind you of the formulas you've used in Excel. While they share similarities, DAX is designed to work with data models and is far more powerful for the kind of analysis we'll be doing.

The most critical concept to grasp in DAX is the difference between a calculated column and a measure. They look similar but behave very differently.

• A Calculated Column is a new column that you add to one of your tables. The value for each row in this column is calculated once when the data is refreshed and is then stored in your model. It's static.
• A Measure is a calculation that is performed "on-the-fly" based on the context of your report. Its result changes dynamically as you interact with slicers, filters, and charts. Measures don't store values in your model; they store formulas.

For financial analysis, you will primarily be using measures, as they allow you to calculate things like total profit, portfolio returns, or growth rates dynamically based on user selections.

To get a clear understanding of this crucial difference, let's watch a video from the experts at SQLBI.

Rule of Thumb: If you need to calculate a value to display in a visual (like a card, chart, or matrix), use a measure. If you need a new category to use in a slicer, on an axis, or to group data, you might need a calculated column. For most financial metrics, measures are the correct choice.

2. Creating Your First Calculations

Let's start by creating a few simple calculations to see DAX in action. We'll use a beginner-friendly tutorial that walks through the process step-by-step.

A. Your First Measure: SUM

Just like in Excel, a common first step is to sum up a column. We'll create a measure to calculate total revenue.

B. A Calculated Column for Row-Level Logic

Next, we'll create a calculated column. Imagine your data has a Revenue and a Cost column for each transaction. A calculated column is perfect for finding the Profit for each individual row (Profit = Revenue - Cost).

C. A Measure Referencing Other Measures

The real power of DAX comes from its composability. You can create simple measures (e.g., Total Revenue and Total Cost) and then combine them in a more complex measure (e.g., Profit Margin).

This video will guide you through creating all three.

Test your understanding!

You have a table of stock transactions with columns for Ticker, Shares, and ExecutionPrice. You want to show the total value of all 'AAPL' transactions in a card visual. Which of the following is the best approach?

1. Create a calculated column TransactionValue = [Shares] * [ExecutionPrice] and then use a slicer for 'AAPL'.
2. Create a measure Total Value = SUMX('Transactions', [Shares] * [ExecutionPrice]) and use a slicer for 'AAPL'.
3. Filter the table for 'AAPL' in the Power Query Editor and then create a measure Total Value = SUM('Transactions'[TransactionValue]).

Show answer

Option 2 is the best approach. A measure is the right tool for an aggregated value that needs to be displayed in a visual. The SUMX function iterates through the table row by row, performs the multiplication, and then sums the result, all while respecting the filter context (like a slicer set to 'AAPL'). Option 1 creates a stored column which is less efficient, and Option 3 permanently filters your data, which is not flexible.

3. Unleashing the Power of CALCULATE

The single most important function in DAX is CALCULATE. It allows you to modify the filter context for a calculation. In simple terms, it lets you answer questions like:

• "What were our total sales... but only for the technology sector?"
• "What is the total revenue... for the same period last year?"

CALCULATE takes an expression (like SUM(Sales[Revenue])) as its first argument, followed by one or more filters.

Let's look at an example.

Revenue from India = CALCULATE( SUM(Financials[Revenue]), Financials[Country] = "India" )

This measure will calculate the sum of revenue, but it will only consider rows where the Country column is "India", overriding any other country selections from slicers or filters on the report.

4. Time Intelligence: Year-over-Year Growth

Now we can apply this to the specific goal for this lesson: calculating Year-over-Year (YoY) growth. This is a fundamental metric in financial analysis, and DAX makes it surprisingly easy. There are two main ways to do this.

Method 1: The "Quick Measure"

Power BI has a feature called Quick Measures that provides a user-friendly interface for creating common DAX calculations without writing code. This is a great way to start, as it aligns with your preference for tool-based learning. Best of all, it generates the DAX formula for you, so you can learn by seeing the code it produces.

To create a YoY growth measure for your total revenue, you would:

1. Right-click on your table and select "New quick measure".
2. Choose "Year-over-year change" from the "Time intelligence" category.
3. Drag your revenue measure (e.g., Total Revenue) to the "Base value" box.
4. Drag your date column (e.g., Calendar[Date]) to the "Date" box.
5. Click OK.

Power BI will generate a new measure and the complex DAX required to calculate it. The result will look something like this in a table visual.

Method 2: The Manual DAX Formula

Once you're comfortable with Quick Measures, you can look at the DAX code they generate to understand the logic. The formula for YoY growth typically uses CALCULATE to get the previous year's sales and DIVIDE to calculate the percentage change.

YoY Growth % = 
VAR CurrentYearSales = SUM(Sales[Amount])
VAR PreviousYearSales = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR))
RETURN
DIVIDE(
    CurrentYearSales - PreviousYearSales, 
    PreviousYearSales
)

You don't need to memorize this now, but it's helpful to see how the CALCULATE and DATEADD functions work together to fetch the data from the prior year, enabling the comparison. This manual approach gives you more flexibility to customize the calculation later.

Conclusion

Excellent progress! You've taken your first steps into DAX, the engine that powers sophisticated analysis in Power BI. By learning the distinction between calculated columns and measures, and by creating your first formulas, you've unlocked a new level of analytical capability.

Key Takeaways:

• DAX is Power BI's formula language, similar to Excel but designed for data models.
• Calculated Columns are static, computed per-row at data refresh, and stored in your model.
• Measures are dynamic, computed on-the-fly, and respond to the filter context of your report. They are the preferred tool for most financial calculations.
• The CALCULATE function is the most powerful tool in DAX, allowing you to modify the filter context of any calculation.
• Quick Measures provide a user-friendly way to perform complex time intelligence calculations like Year-over-Year growth and are a great way to learn the underlying DAX code.

In our next lesson, we will explore how to create custom tooltip pages. You'll see how the powerful measures you created today, like YoY Growth, can be embedded into these tooltips to provide deep, contextual information when a user hovers over a data point on a chart, making your dashboards even more insightful.