How to Use Formula in Power BI

Formulas in Power BI are the key to transforming data from simple lists of numbers into genuinely useful business insights. While the idea of writing formulas can feel intimidating, it’s much more approachable than you might think. This guide will walk you through the essential concepts behind Power BI formulas, explaining how to create them step-by-step to answer your most important business questions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Let's Talk About DAX

When someone mentions formulas in Power BI, they're talking about DAX, which stands for Data Analysis Expressions. Think of DAX as the formula language that lives inside Power BI (as well as other Microsoft tools like Power Pivot in Excel). It’s a library of functions and operators that you can combine to build formulas and expressions.

You don’t need to be a programmer to use DAX. Many of its functions will feel familiar if you've ever used spreadsheet formulas, like SUM, AVERAGE, and IF. The real power of DAX is its ability to perform calculations over your data tables and respond dynamically to how a user interacts with a report.

Calculated Columns vs. Measures: The Two Types of Formulas

Before you write your first formula, it's important to understand the two main ways DAX calculations are used in Power BI: calculated columns and measures. Getting this distinction right is the most critical first step.

What is a Calculated Column?

A calculated column is exactly what it sounds like: a brand new column that you add to one of your existing tables. The formula for a calculated column is computed row by row during the data refresh process. Once calculated, the values are stored in your model just like any other column of data, taking up space in your file.

• How it works: It calculates a value for each individual row in your table. It has "row context," meaning it can see the values in other columns of that same row to perform its calculation.
• Example: If you have a 'Sales' table with Quantity and Unit Price columns, you can create a calculated column called Revenue with the formula =[Quantity] * [Unit Price]. Power BI would perform this multiplication for every single row in the sales table and store the result.
• When to use one: Use a calculated column when you want to define a fixed value for each row. This is perfect for when you want to use the result as a filter, slicer, or on the axis of a chart (e.g., categorizing sales into "Large" vs. "Small" based on revenue).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

What is a Measure?

A measure is a formula for a calculation that is performed on the fly. Unlike a calculated column, a measure’s result isn't stored in your table. Instead, it’s calculated in real-time based on the context provided by your report - like filters, slicers, and whatever visual it's being used in.

• How it works: It takes a set of rows (determined by the report context) and aggregates them. It uses "filter context." It doesn't live on any specific row, it lives "above" the table.
• Example: To calculate total revenue, you'd create a measure like Total Revenue = SUM(Sales[Revenue]). If you put this measure on a card visual, it shows the sum of all revenue. If you put it in a bar chart with 'Product Category' on the axis, it automatically calculates the total revenue for each category.
• When to use one: Use a measure for any calculation that needs to represent an aggregation of data, such as sums, averages, counts, percentages, or ratios. If the question is "How much..." or "What percentage of...", you almost always need a measure. Measures are the bedrock of most useful Power BI reports.

How to Create a Formula in Power BI: Calculated Columns

Let's create a calculated column step-by-step. Imagine we have a sales table with columns for Product, OrderQuantity, and UnitPrice but no Total Revenue for each transaction.

Step 1: Navigate to the Data View

In Power BI Desktop, look at the left-hand side and click on the "Data" icon (it looks like a small spreadsheet grid). This view shows you the raw data within your tables.

Step 2: Select Your Table and Add a New Column

From the Fields pane on the right-hand side, click on the table you want to modify (e.g., 'Sales'). This will make its data visible in the main window. Now, in the ribbon menu at the top, select the "Table tools" tab and click the "New column" button.

Step 3: Write Your DAX Formula

A formula bar will appear below the ribbon, similar to Excel's. Here, you'll enter your DAX formula. By default, it will say "Column = ". You want to rename "Column" to something descriptive, like Revenue, and then type your expression after the equals sign.

Type the following and press Enter:

Revenue = Sales[OrderQuantity] * Sales[UnitPrice]

• Table Name Reference: In DAX, you refer to columns by both their table name and the column name, like Sales[OrderQuantity]. Power BI often automatically adds the table name as you start typing the column name. This is best practice as it avoids confusion if you have columns with the same name in different tables.
• IntelliSense: As you type, Power BI will helpfully suggest function names and column names, saving you time and preventing typos.

Step 4: Format Your New Column

Once you hit Enter, you'll see your new 'Revenue' column appear in the table. Power BI will calculate the value for every row instantly. You'll probably want to format it as currency. With the new 'Revenue' column still selected, go to the "Column tools" tab in the ribbon. Here you can change the format from "General" to "Currency" and select the appropriate currency symbol.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How to Create a Formula in Power BI: Measures

Now that we have a Revenue for each sale, let's create a measure to calculate the total revenue. Measures are used to create aggregations like sums and averages.

Step 1: Go to the Report View

On the left-hand side, click the "Report" icon (it looks like a bar chart). This is the canvas where you build your report visuals. While you can create measures in the Data or Model view, it’s most common to do it here.

Step 2: Create a New Measure

In the Fields pane on the right, right-click on the 'Sales' table and select "New measure." Alternatively, you can click on the table name and then click the "New measure" button in the "Table tools" ribbon that appears.

Step 3: Write Your DAX Formula

The same formula bar will appear. This time, type the following DAX formula and press Enter:

Total Revenue = SUM(Sales[Revenue])

This tells Power BI to use the SUM function to add up all the values in the Revenue column of the Sales table.

Step 4: See Your New Measure Appear

After you press Enter, something different happens. You don't see anything new added to your table. That’s because measures work differently! Instead, look in your Fields pane. You'll see Total Revenue listed under the 'Sales' table, but it will have a small calculator icon next to it, which distinguishes it from a regular column.

Step 5: Use Your Measure in a Visual

Now, let’s see the measure in action. From the Visualizations pane, select the "Card" visual to add an empty card to your report. Next, simply drag your [Total Revenue] measure directly from the Fields pane onto the card. Voila! The card will display the aggregated total of all revenue. If you drop it into a bar chart with 'Country' on the axis, it will show you total revenue by country - automatically calculated and flexing to its new context.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Essential DAX Functions to Get Started

The DAX language has hundreds of functions, but you only need a handful to get real work done. Here are a few foundational ones to learn:

Aggregation Functions

• SUM(): Adds up all the numbers in a column.
• AVERAGE(): Calculates the average of a column of numbers.
• COUNT(): Counts the number of rows in a column.
• DISTINCTCOUNT(): Counts the number of unique values in a column (e.g., how many unique customers made a purchase).

Logical Functions

• IF(): The tried-and-true conditional statement. It checks if a condition is met and returns one value if true, and another if false. It's great for calculated columns.

Order Size = IF(Sales[Revenue] > 1000, "Large", "Small")

CALCULATE(): The Superpower of DAX

If there’s one function to learn, it’s CALCULATE(). It lets you modify the "filter context" of a calculation. In simple terms, it lets you apply your own filters inside a formula. It’s what allows for complex time intelligence (like Year-over-Year growth) and slicing data in endlessly useful ways.

Its basic syntax is: CALCULATE ( [Your Expression], [Filter] )

For example, if you wanted to create a measure that only shows revenue for one specific product, you could write:

Mug Sales = CALCULATE ([Total Revenue], 'Product'[Product Name] = "Coffee Mug")

This measure will always show the total revenue just for coffee mugs, no matter what other filters are applied on the report page.

Final Thoughts

Learning to use formulas in Power BI is a process of understanding the foundational difference between calculated columns for row-by-row context and measures for dynamic aggregations. Starting with simple functions and building from there can unlock a level of customized, actionable reporting far beyond what an unmodified dataset can offer.

DAX is undeniably powerful, but it also has a learning curve. For businesses needing to connect data sources and get dashboards built fast - without learning a new formula language - we built Graphed. You can connect your marketing and sales data, then just describe the charts and reports you want to see in plain English. Graphed’s AI generates real-time, interactive dashboards instantly, freeing you up to focus on the insights, not the formulas.