Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX Calculated Columns


Share

What is a Calculated Column?

A calculated column is a new column that you add to a table with a DAX formula.

You write the formula once.

Power BI calculates it for every row of the table and stores the results in the model, just like the columns you loaded.

Use a calculated column when you need a value for each row: to show it in a table, to group by it, or to use it in a slicer or filter.

This page uses the Poke Mart model described in DAX Introduction.


Row Context: One Row at a Time

A calculated column is calculated one row at a time.

For each row, the formula sees the values in that row.

This is called row context: it simply means "the current row".

In the formula below, Products[UnitPrice] means the unit price in the current row, not the whole column:

Example

Margin per Unit = Products[UnitPrice] - Products[UnitCost]

For the Poke Ball row, the formula calculates 200 - 110 = 90. For the Great Ball row, it calculates 600 - 330 = 270. And so on, for all 10 products.

This works like an Excel formula copied down a column, where each row uses the cells in its own row.


Create a Calculated Column

Add the Margin per Unit column to the Products table:

  1. Select Table view on the left side of Power BI Desktop.
  2. In the Data pane, select the Products table.
  3. On the Table tools tab, select New column. You can also right-click Products in the Data pane and select New column.
  4. The formula bar opens with the text Column = . Replace the whole text with the formula below.
  5. Press Enter.

Example

Margin per Unit = Products[UnitPrice] - Products[UnitCost]

Table view in Power BI Desktop with the Products table selected, the New column button on the Table tools tab, the Margin per Unit formula in the formula bar, and the new column at the right of the table

The new column appears at the right end of the table.

The numbers below have a thousands separator.

In Table view, Power BI shows them without one, for example 1200:

ABCProductIDABCProductABCCategory123UnitPrice123UnitCost123Margin per Unit
P01Poke BallBalls20011090
P02Great BallBalls600330270
P03Ultra BallBalls1,200700500
P04PotionMedicine300150150
P05Super PotionMedicine700380320
P06Hyper PotionMedicine1,200680520
P07ReviveMedicine1,500900600
P08AntidoteStatus Healers1004555
P09Paralyze HealStatus Healers20095105
P10AwakeningStatus Healers250120130

Ultra Ball and Hyper Potion have the same price, 1,200, but the shop earns 20 more on each Hyper Potion.

Revive earns the most per unit: 600.

In the Data pane, the new column has a special icon.

It shows that the values come from a formula.

You can use Margin per Unit like any other column: in a table visual, on the axis of a chart, or in a slicer.



Why Sales[Quantity] * Products[UnitPrice] Does Not Work

The Sales table has a Quantity column, but no prices.

The prices are in the Products table.

So it seems natural to add this column to Sales:

Example (does not work)

Amount = Sales[Quantity] * Products[UnitPrice]

Try it: in Table view, select the Sales table, select New column, enter the formula and press Enter.

Power BI shows an error under the formula bar.

It starts with "A single value for column 'UnitPrice' in table 'Products' cannot be determined."

The Amount column is still created, but every cell shows #ERROR, and the Data pane shows a warning icon next to Amount.

Here is why.

The row context is on the Sales table.

For each Sales row, DAX knows the Quantity of that row.

But Products[UnitPrice] is a column in another table, with 10 different prices.

DAX does not know which of the 10 prices you mean.

You know the answer: the price of the product in the current order.

Each Sales row has a ProductID, and the relationship between Sales and Products connects it to exactly one product.

But a plain column reference does not follow relationships.

You have to ask for that with the RELATED function:

Example

Amount = Sales[Quantity] * RELATED(Products[UnitPrice])

You create this column in the next chapter.

Note: If you tried the formula, leave the Amount column with the errors.

In the next chapter, you replace its formula.


Calculated Column or Power Query?

In the Power Query Columns chapter, you added a Sales column with Add Column > Custom Column.

That also gives a value for each row.

So what is the difference?

Power Query custom columnDAX calculated column
LanguageMDAX
CalculatedWhile the data is loadedFrom the data already in the model
Using other tablesUsually by merging the queries first, as in Power Query MergeThrough relationships, with RELATED

For simple calculations, both work.

In this chapter we use DAX, because the model already has the tables and the relationships.

Note: Calculated columns are stored in the model, so every calculated column makes the file bigger.

That does not matter for 10 products, but it does for a table with millions of rows.

For totals and ratios, use measures instead.

You learn them in DAX Measures.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.