Power BI calculated column [With 71 Useful Examples]

In this Power BI Tutorial, we will discuss the calculated column in Power BI. And also we will see how to create a calculated column with different examples of Power BI calculated columns:

• Power bi calculated column
• Power bi calculated column vs measure
• Power bi calculated column if
• Power bi calculated column aggregate
• Power bi calculated column average
• Power bi calculated column average by category
• Power bi calculated column add two columns
• Power bi calculated column another table
• Power bi calculated column if and
• Power bi calculated column moving average
• Power bi calculated column cumulative sum
• Power bi calculated column count rows
• Power bi calculated column count if
• Power bi calculated column count
• Power bi calculated column distinct count
• Power bi calculated column divide
• Power bi calculated column divided by zero
• Power bi calculated column contains text
• Power bi calculated column concatenate
• Power bi calculated column left
• Power bi calculated column as legend
• Power bi calculated column age
• Power bi calculated column earlier
• Power bi calculated column empty
• Power bi calculated column max value
• Power bi calculated column if an error
• Power bi calculated column from an unrelated table
• Power bi calculated column grand total
• Power bi calculated column in the matrix
• Power bi calculated column index
• Power bi calculated column if null
• Power bi calculated column date format
• Power bi calculated column dateadd
• Power bi calculated column days between two dates
• Power bi calculated column earliest date
• Power bi calculated column latest date
• Power bi calculated column last year
• Power bi calculated column last month
• Power bi calculated column year
• Power bi calculated column month name
• Power bi calculated column week number
• Power bi calculated column month and year
• Power bi calculated column previous month
• Power bi calculated column fiscal year
• Power bi calculated column today’s date
• Power bi calculated column YTD
• Power bi calculated column multiple if statement
• Power bi calculated column multiply
• Power bi calculated column number format
• Power bi calculated column nan
• Power bi calculated column number to text
• Power bi calculated column percentage of total
• Power bi calculated column percentage
• Power bi calculated column previous row
• Power bi calculated column decimal places
• Power bi calculated column sum by group
• Power bi calculated column switch
• Power bi calculated column selected value
• Power bi calculated column sum if
• Power bi calculated column subtraction
• Power bi calculated column true false
• Power bi calculated column time difference
• Power BI calculated column use relationship
• Power BI calculated column based on a measure
• Power bi calculated column based on another column
• Power bi calculated column between two tables
• power bi calculated column count by group
• Power bi calculated column date from DateTime
• Power bi calculated column rankx

Power bi calculated column

Calculated columns allow you to add new data to tables that already exist in your model. However, instead of querying and reading the value from the data source into the new column, create a Data Analysis Expression (DAX) expression that defines the value in the column. In Power BI Desktop, calculated columns are created using the new column feature in Report View.

The calculated column you create in Power BI appears like any other field in the Fields list, but with a special icon that indicates that the value is the result of a formula. Like any other field, you can give the column any name you like and add it to your report visualization.

Power bi calculated column vs measure

Here we will see the difference between Power BI calculated column vs the measure.

Calculated column	Measure
The calculated column returns multiple results	It returns a single result
The calculation is done row by row	The calculation is done in response to filter and field changes in the report
It needs memory to store	It doesn’t need memory to store
The calculation is done when the report is refreshed or when it is created.	Get calculated when added to the report through something like a card visual
It increases the file size	It doesn’t increase the file size
One can see the calculated column result in the Data view	You can see the Measure view when you add to the visual.
It does not provide a weighted average of a column	It provides a weighted average

Read Power BI sync slicers

Power bi calculated column if

Here we will see an example of the Power BI calculated column if.

We will use the below table, and we will create a calculated column that will calculate if the Amount is more than 600, then return ‘Good’, else return Not Good’.

• In Power BI Desktop, go to the Modelling tab -> New Column to create a calculated column.
• Then in the Formula box write the below formula:

Column = If('Table'[Amount]>=600,"Good", "Not Good")

Now, go to the data view, you can see the calculated column is added to the table, it shows the value based on calculation.

This is an example of power bi calculated column if.

Read Line Chart in Power BI

Power bi calculated column aggregate

Here we will see an example of Power Bi calculated column aggregate.

For example, we will use the below table, and we will calculate the sum of the Amount.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then in the Formula box write the below formula:

Total Amount = SUM('Table'[Amount])

This is an example of Power bi calculated column aggregate.

Read Power BI Group By

Power BI calculated column average

Here we will see an example of Power BI calculated column average.

For example, we will use the below table and we will create a calculated column to calculate the average of the total amount.

• In Power BI, go to the Modeling tab -> select the New column from the ribbon.
• Then write the below formula in the Formula box:

Average Amount = AVERAGE('Table'[Amount])

This is an example of a power bi calculated column average.

Power bi calculated column average by category

Here we will see an example of Power BI calculated column average by category.

For example, we will use the below table, and we will create a new column to calculate the Average Amount based on the Rate category.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

AverageAmount based on Rate category = CALCULATE (
    Average ( 'Table'[Amount]),
    ALLEXCEPT ( 'Table','Table'[Rate] )
)

• Now you can see the new column added to the table having the Average of Amount for Rate category.

This is an example of power bi calculated column average by category.

Read Power bi conditional column

Power bi calculated column add two columns

Here we will see an example of a Power BI calculated column adding two columns.

For example, we have the below table, and we will create a calculated column that will add the Amount and tax column.

• In Power BI, go to the Modelling tab -> click on the New column from the ribbon.
• Then provide the below formula in the Formula box:

Total Amount = 'Table'[Amount] +'Table'[Tax]

• Now you can see you have added the column to the table with added result of Amount and tax.

This is an example of a power bi calculated column add two columns.

Power BI calculated column another table

Here we will see an example of Power BI calculated column another table.

For example, we have the below table, and we want to add a column that contains values by extracting the sales values based on id from table 2 to table 1.

Table 1

Table 2:

• In Power BI Desktop, click on the Modeling tab -> then click on the New column from the ribbon.
• Next, write the below formula in the formula box:

Sum_Tab2 = 
var t2_ID = Person[Id]

return
CALCULATE(
    SUM('Table (2)'[Sales]),
    'Table (2)'[Id] = t2_ID
)

• Now you can see the sales value column added to the table:

This is an example of a power bi calculated column in another table.

Read Power BI date hierarchy

Power BI calculated column if and

Here we will see an example of Power BI calculated column if and.

For example, we will use the below table, and we will create a new column to check if the age is greater than equal to 30, that person is not eligible, or else it is eligible.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Criteria = IF(Person[Age]>=30, "Not Eligible","Eligible")

• Now you can see the column gets added to the table based on the formula.

This is an example of Power BI calculated column if and.

Power bi calculated column moving average

Here we will see an example Power BI Calculated column moving average.

The moving averages approach carries the average of the time series’ most current k data values. It’s called moving because it replaces the oldest observation in the equation with the most recent value and calculates a new average every time a new observation becomes available for the time series.

As a result, the duration over which the average is calculated shifts with each passing duration.

For example, we will use the below table and we will calculate the moving average of 3 months.

• In Power BI Desktop, go to the Modelling tab, and select the New column from the ribbon.
• Then write the below formula in the formula box:

Moving_Average_3_Month = CALCULATE (
    AVERAGEX ( ALLSELECTED('MovingAverage'), 'MovingAverage'[Amount]),
    DATESINPERIOD (
        'MovingAverage'[Date],
        LASTDATE ( 'MovingAverage'[Date] ),
        -3,
        MONTH
    )
)

• Now you can see the column get added to the table which contains the value of the Moving average of 3 month

This is an example of Power bi calculated column moving average.

Read Power BI Date Slicer

Power bi calculated column cumulative sum

Here we will see an example of Power Bi calculated column cumulative sum.

For this example, we will use the below Shift table for multiple employees, and we will calculate the cumulative sum based on the Score and half the cumulative sum for each row where the workday is False.

• In Power BI Desktop, go to the Modelling tab -> click on the new column from the ribbon.
• Then write the below formula in the Formula box:

Cumulative Sum = 
VAR OuterDate = Shifts[Date]
RETURN
    SUMX (
        CALCULATETABLE (
            Shifts,
            ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
            Shifts[Date] <= OuterDate
        ),
        VAR InnerDate = Shifts[Date]
        RETURN
            POWER (
                0.5,
                CALCULATE (
                    COUNTROWS ( Shifts ),
                    ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
                    Shifts[Date] <= OuterDate,
                    Shifts[Date] >= InnerDate,
                    NOT ( Shifts[Workday] )
                )
            )
                * Shifts[Score]
    )

• Next, you can see the column added having the cumulative sum based on the formula.

This is an example of Power bi calculated column cumulative sum.

Read How to create a report in Power BI Desktop?

Power bi calculated column count rows

Here we will see an example of Power BI calculated column count rows.

For example, we will use the below table and we will add a column that will count the number of rows present in the table.

• In Power Bi Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the Formula box:

Number of person = COUNTROWS(Person)

• Now you can see a column get added to the table having the number of rows present in the table.

This is an example of Power BI calculated column count rows.

Power bi calculated column count if

Here we will see an example of Power bi calculated column count if.

As we know the countIf function is not available in Power BI, it is an excel function. Instead of Count If the function, we can apply the same logic using 2 functions:

1. CountA Function: Basically it will count the number of values in the column, but it does not count the null or blank value.
2. CountRows Function: This function counts the number of rows present in the column of the table.

For example, we will use the below table and we will count the number of people, whose age is greater than 25.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the Formula box:

Age>25 = CALCULATE(COUNTROWS(Person), Person[Age]>=25)

• Now you can see the column get added and have the value 1, person age> 25.

This is an example of Power bi-calculated column count if.

Read Power BI split column

Power bi calculated column count

Here we will see an example of the Power BI calculated column count function.

For example, we will use the below table and create a new calculated column to count the number of people.

• In Power Bi Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the Formula box.

Number of person = COUNT(Person[Person])

• Next, you can see the column added to the table having the number of personal values.

This is an example of a Power bi-calculated column count.

Power bi calculated column distinct count

Here we will see an example of Power BI calculated column distinct count.

For example, we will use the below table and count the unique person’s name present in the table.

• In Power BI Desktop, go to the Modelling tab -> select the new column from the ribbon.
• Then write the below formula in the Formula box:

Number of person = DistinctCOUNT(Person[Person])

• Next, you can see the calculated column added to the table having the number of unique names.

This is an example of a Power BI calculated column distinct count.

Read Power Query Date

Power bi calculated column divide

Here we will see an example of Power BI calculated column divide.

For example, we will use the below table and we will calculate the profit and profit percentage.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula to get the Profit.

Profit = 'Product'[SP]-'Product'[CP]

• Next, create another new calculated column to calculate the profit percentage using the below formula:

Profit percentage = DIVIDE('Product'[Profit]*100,'Product'[CP],0)

Now you can see the Profit column and Profit Percentage column are added to the table.

This is an example of a power bi calculated column divide.

Read Power Query Examples

Power bi calculated column divided by zero

Here we will see an example of a Power BI calculated column divided by zero.

For example, we will use the below table, and we will use column 1 as the numerator and column 2 as the denominator, which contains a 0 value.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box:

Result= DIVIDE('Table (3)'[Column1],'Table (3)'[Column2],0)

• Now, you can see the column added to the table based on the above formula.

This is an example of a Power BI calculated column divided by zero.

Power bi calculated column contains text

Here we will see an example of a Power BI calculated column containing the text.

For example, we will use the table below and create a new column that will check whether the Person column contains the ‘Person 1’ value and return ” contains the text”, or else “does not contain the text”.

• In Power BI Desktop, go to the Modeling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box.

Text or not = if (Person[Person]= "Person 1", "Contains text", " does not contain text")

This is an example of a Power bi-calculated column containing text.

Read Create table using Power Query in Power BI

Power bi calculated column concatenate

Here we will see an example of Power BI calculated column concatenate.

For example, we will use the below table and we will concatenate the Person column and the id column.

• In Power BI Desktop, go to the Modelling tab -> then click on the New column from the ribbon.
• Then write the formula in the Formula box:

id and person = Person[Id] & "-" & Person[Person]

• Now you can see the new column added to the table having the concatenated value.

This is an example of a Power bi-calculated column concatenate.

Power BI calculated column left

Here we will see an example of the Power BI calculated column left.

For example, we will use the below table and we will get the left 3 letters from the Day name column. Let’s say the Day name is Monday, so the first 3 letters will be ‘Mon’.

• In Power BI Desktop, go to the Modeling tab, then click on the New column from the ribbon.
• Then write the below formula in the formula box:

Column = LEFT('Table 6'[Days name],3)

This is an example of the Power BI calculated column left.

Read Power BI integration with PowerApps Portals

power bi calculated column as legend

Here we will see an example of a Power BI calculated column as a legend.

For example, we will use the below table and we will use the Criteria calculated column as a legend in the Stacked column chart.

• In Power BI Desktop, select the stacked bar chart in the visualization pane.
• Then provide the Age field as Axis, Criteria (calculated column) as Legend, and Sales field as value.

This is an example of a Power BI calculated column as a legend.

Power bi calculated column age

Here we will see an example of Power BI calculated column age.

For example, we will use the below table and we will calculate the Age of each person. Here, I have DOB column that’s contains the date of birth and we will calculate the age from the DOB column.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box:

Age = DATEDIFF(Person[DOB],TODAY(),YEAR)

This is an example of Power bi calculated column age.

Read Power BI if date + 27 Examples

Power bi calculated column earlier

Here we will see an example of the Power BI calculated column earlier.

For example, we will use the below table, and we will sum the amount for each product.

• In Power BI Desktop, go to the Modeling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Total for product = CALCULATE(SUM('Table 5'[Amount]),
           FILTER(ALL('Table 5'),
            'Table 5'[Product]=EARLIER('Table 5'[Product])
                 )
          )

• Now you can see the table added to the table having the total amount for each product.

This is an example of a Power bi-calculated column earlier.

Power bi calculated column empty

Here we will see an example of a Power BI calculated column empty.

For example, we will use the below table and we will check any empty or blank values in the Sales column. if there are blank or empty values it returns true or else false.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the Formula box:

Column = ISBLANK('Person'[Sales])

Now you can see the column added to the table has true and false values based on whether the sales column is empty or not.

This is an example of a Power BI calculated column empty.

Read Power BI IF + 31 Examples

Power BI calculated column max value

Here we will see an example of Power BI calculated column max value.

For example, we will use the below table and we will get the max amount value using Dax in a calculated column.

• In the Power BI desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Max Amount = MAX('Table 5'[Amount])

• Now you can see the max value in the newly added column to the table in Power BI.

This is an example of a Power Bi calculated column max value.

Power bi calculated column if an error

Here we will see an example of Power BI calculated column if an error.

For example, we will use the below table, and we will divide column 1 with column 2. If we get an error in the output, then return 0, else return the output.

• In Power BI Desktop, go to the Modeling tab -> then click on the New column from the ribbon
• Then write the below formula in the formula box:

If error eq 0 = IFERROR('Table (3)'[Column1]/'Table (3)'[Column2],0)

Now you can see the column added to the table having 0 value if there is an error.

This is an example of Power Bi calculated column if an error.

Read Power BI Change Data Type of a Column

Power bi calculated column from an unrelated table

Here we will see an example of the Power BI Calculated column from another table.

For example, we will use two unrelated tables, and we will create a sales column in Table 1 by using LookupValue() in a calculated column.

Table 1:

Table 2:

• In the Power Bi desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then in the Formula box write the below formula:

Sales = 
LOOKUPVALUE (
    'Table (2)'[Sales],
   'Table (2)'[Id] , Persons[Id]
)

• Now you can see a new column added to the table having sales value.

This is an example of Power bi calculated column lookup value

Power bi calculated column grand total

Here we will see an example of Power BI calculated column grand total.

For example, we will use the below table and we will calculate the grand total of sales and grand total percentage using the calculated column in Power BI.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box to calculate the grand total sales:

Grand total sales = SUM('Person'[Sales])

• Now, to calculate the grand total percentage, create another New column.
• Then write the below formula in the Formula box:

% of Grand Total = 
DIVIDE (
    SUM ( Persons[Sales]),
    CALCULATE ( SUM ( Persons[Sales] ), ALL ( Persons[Person]) )
)

• Now you can see two columns get added to the table, one for grand total and another for grand total percentage.

This is an example of Power Bi calculated column grand total.

Read Access to the resource is forbidden error in Power BI

Power bi calculated column in the matrix

Here we will see an example of the Power BI calculated column in the Matrix

For example, we will use the below table, so in this table, we have two calculated columns ( Profit and Profit percentage) and we will add these two calculated columns to Matrix visual.

Profit = 'Product'[SP]-'Product'[CP]

Profit percentage = DIVIDE('Product'[Profit]*100,'Product'[CP],0)

• In the Power BI Desktop report view, select the Matrix visual from the visualization pane.
• Then in the Row field, -> drag the Product, SP, CP and in value drag the Profit and Profit percentage column.
• Then in the Format pane, under ‘Row header’, turn off the Stepped layout.

This is an example of Power BI calculated column in the matrix.

Power bi calculated column index

Here we will see an example of a Power BI calculated column index.

For example, we will use the below table and we will create a calculated column to provide the index based on the total value and customer. We will set the highest value as index 1 and so on.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the DAX formula in the formula box:

SuperRank = 
    VAR __table = FILTER(ALL('Table6'),[Total] > EARLIER('Table6'[Total]))
    VAR __tableSame = FILTER(ALL('Table6'),[Total] = EARLIER('Table6'[Total]) && [Customer] < EARLIER(Table6[Customer]))
RETURN
    COUNTROWS(__table) + 1 + COUNTROWS(__tableSame)

• Now, you can see a new column added to the table, which provides the index value based on the total value and customer.

This is an example of Power BI calculated column index.

Read Power BI convert hours to minutes

Power BI calculated column if null

Here we will see an example of a Power BI calculated column if null.

For example, we will use the below table, and we will check if the value of the CP column is null or blank then it returns zero.

• In Power BI Desktop, go to the Modeling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box.

Column = if('Product (2)'[CP] = 0 || BLANK(), 0, 'Product (2)'[CP])

• Now you can see a column added to the table having 0 value instead of null values.

This is an example of power bi calculated column if null.

Power bi calculated column date format

Here we will see an example of Power BI calculated column date format.

For example, we will use the below table and we will format the below date column as MM-dd-yyyy.

• In Power BI Desktop, go to the Modeling tab -> click on the New column from the ribbon.
• Then write the below formula in the Formula box.

Formated date = FORMAT(MovingAverage[Date], "MM-dd-yyyy")

• Now you can see the new column added to the table having the formatted date.

This is an example of Power BI calculated column date format.

Read Power BI convert yyyymmdd to date

Power BI calculated column dateadd

Here we will see an example of Power BI calculated column date add.

For example, we will use the below table and we will add two days to the date column.

• In Power Bi Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Dateadd 2 days = Format (MovingAverage[Date] +2, "dddd")

• Now you can see a new column added to the table which contains days’ names having 2 days ahead of the date column.

This is an example of Microsoft Power BI calculated column dateadd.

Power bi calculated column days between two dates

Here we will see an example of Power BI Calculated column days between two dates.

For example, we will use the below table, and we will calculate the number between ship date and order date.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box:

Day = DATEDIFF('Product'[Order date],'Product'[Ship date],DAY)

• Now you can see the new column added to the table having the number of days.

This is an example of Power Bi calculated column days between two dates.

Read Get Month Name from Month Number in Power BI

Power bi calculated column earliest date

Here we will see an example of Power BI calculated column earliest date.

For example, we will use the below table and we will get the earliest date based on Email by and Email subject.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Earliest Date = var Emailby ='Table 4'[Email by]
var EmailSubject = 'Table 4'[Email Subject]
Return
MINX( Filter(ALL('Table 4'),'Table 4'[Email by]=Emailby && 'Table 4'[Email Subject]=EmailSubject), 'Table 4'[Date])

• Now you can see the calculated column added to the table, having the earliest date for Email by and Email subject.

This is an example of a Power bi-calculated column earliest date.

Power bi calculated column latest date

Here we will see an example of Power BI calculated column latest date.

For example, we will use the table below and create a calculated column, in which we will use the Max () to get the latest date from the date column.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Latest order date = MAX('Product'[Order date])

• Now, you can see the column added to the table having the latest date in Power BI.

This is an example of power bi calculated column latest date.

Read Power bi shared dataset permissions management

Power bi calculated column last year

Here we will see an example of the Power BI calculated column last year.

For example, we will use the below table, and we will create a calculated column that will calculate the total amount of the previous year based on product and customer.

• In Power BI Desktop, go to the Modelling tab, and select the New column from the ribbon.
• Then write the below formula in the formula box.

PY_Price = 
CALCULATE (
    SUM ( 'Table 6'[Amount] ),
    ALLEXCEPT ('Table 6','Table 6'[Customer], 'Table 6'[Product] ),
    PREVIOUSYEAR( ('Table 6'[Date] )
))

• Now you can see the column added to the table, having the previous year’s total amount based on Customer and product.

This is an example of Power bi calculated column last year.

Read Power BI nan error (Not a number) while dividing by Zero

Power bi calculated column last month

Here we will see an example of the Power BI calculated column last month.

For example, we will use the below table and we will calculate the total sales for the previous month based on product and customer.

• In Power BI Desktop go to the Modeling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box:

PM_Price = 
CALCULATE (
    SUM ( 'Table 6'[Amount] ),
    ALLEXCEPT ('Table 6','Table 6'[Customer], 'Table 6'[Product] ),
    PREVIOUSMONTH( ('Table 6'[Date] )
))

• Now you can see the column added to display the total amount for the previous month.

This is an example of Power bi calculated column previous month.

Power bi calculated column year

Here we will see an example of Power BI calculated column year.

For example, we will use the below table, to get the year from the date column using DAX.

• In Power BI Desktop, go to the Modeling tab, then select the New column from the ribbon.
• Then write the below formula in the formula box.

Year = YEAR(MovingAverage[Date])

• Now you can see the column get added to the table having year value.

This is an example of Power bi calculated column year.

Read Power BI report using People Picker Field

Power bi calculated the column month name

Here we will see an example of Power BI calculated column month name.

For example, we will use the below table and we will create a calculated column to get the Month name using DAX.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Month name = FORMAT('Table 4'[Date],"MMMM")

• Now you can see the calculated column get created having the Month name.

This is an example of Microsoft power bi calculated column month name.

Power bi calculated column week number

Here we will see an example of Power BI calculated column week number

For example, we will use the below table and get the week number based on the date column.

• In Power BI, go to the modeling tab, then click on the New column from the ribbon.
• Then write the below DAX formula in the formula box:

Week num = WEEKNUM(MovingAverage[Date])

• Now you can see the calculated column having the week number.

This is an example of Power bi calculated column Weeknum.

Read Data Labels in Power BI

Power bi calculated column month and year

Here we will see an example of Power BI calculated column month and year.

For example, we will use the below table to get the month and year using Dax in a calculated column.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the Formula box:

Month and year = Format('Table 4'[Date], "MM-yyyy")

• Now, you can see the column added to the table contains the Month and Year from the date column in Power BI.

This is an example of power bi calculated column month and year.

Power bi calculated column fiscal year

Here we will see an example of Power BI calculated column fiscal year or Financial year.

For this example, we will use the sample Financial table, and we will calculate the financial year from the date column. In the date column the date from April 2021 to April 2022 then the output should be FY21/22.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Financial Year = 
VAR fy =
    IF (
        MONTH ( Table1[Date]) <= 3,
        VALUE ( FORMAT ( Table1[Date], "YY" ) ) - 1,
        VALUE ( FORMAT ( Table1[Date], "YY" ) )
    )
RETURN
    CONCATENATE ( "FY", CONCATENATE ( fy, CONCATENATE ( "/", fy + 1 ) ) )

• Now you can see the Financial year column added to the table in Power BI.

This is an example of power bi calculated column fiscal year.

Power bi calculated column today’s date

Here we will see an example of the Power BI calculated column on today’s date.

• In the Power BI desktop, to get today’s date in the calculated column, go to the Modelling table -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Today's date = TODAY()

This is an example of Power bi calculated column today’s date.

Read Power bi table visualization

Power bi calculated column YTD

Here we will see an example of Power BI calculated column YTD.

For example, we will use the sample financial table and we will calculate year-running total sales.

• In Power BI Desktop, go to the modeling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Calculated Column = TOTALYTD ( SUM ( Table1[ Sales] ) , Table1[Date] )

This is an example of Power bi calculated column YTD.

Power BI calculated column multiple if statement

Here we will see an example of a Power BI calculated column multiple if statement.

For example, we will use the below table and we will create a range calculated column based on the sales range.

• In Power BI Desktop, go to the Modelling tab-> click on the New column from the ribbon.
• Then write the below formula in the Formula box:

Range = IF('Table 9'[Sales]>=10 && 'Table 9'[Sales] <= 30, "10-30", IF('Table 9'[Sales] >= 30 && 'Table 9'[Sales] <= 40, "30-40", IF('Table 9'[Sales] >= 40, "greater than 40",BLANK())))

• Now you can see the new column added to the table having range based on sales.

This is an example of Power BI calculated column multiple if statement.

Power bi calculated column multiply

Here we will see an example of Power BI calculated column multiply.

For example, we will use the below table and we will multiply the SP and quantity.

• In Power BI Desktop, go to the Modeling tab -> then select the New column from the ribbon.
• Then write the below formula in the formula box:

Result = 'Product (2)'[SP]*'Product (2)'[Quantity]

This is an example of Power BI calculated column multiply.

Power bi calculated column number format

Here we will see an example of Power BI calculated column number format.

For example, we will use the below table, the sales column is the calculated column, and we will format the sales column as a number.

• To format the sales calculated column as a number in Power BI, select the column -> to go to the Column tools tab -> then change the format General to the Whole number.

This is an example of the Power Bi calculated column number format.

Power bi calculated column nan

Here we will see an example of Power BI calculated column nan.

For example, we will use the below table, here we will replace Nan with 0. Basically in visual, we will get Nan, when we will divide 0 by 0.

• In Power BI Desktop, go to the Modelling tab -> then click on the new column from the ribbon.
• Then write the below formula in the formula box:

Result = if('Order'[Amount 2]=0,0,'Order'[Amount 1]/'Order'[Amount 2])

• Now I added the calculated column to the Card visual, it is showing a 0 value.

This is an example of Power BI calculated column nan.

Power BI calculated column number to text

Here we will see an example of Power BI calculated column number to text.

For example, we will use the below table and we will convert the result column from a decimal number to text.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Column = CONVERT('Order'[Result],STRING)

• Now you can see the data type of the newly calculated column changed to text.

This is an example of Power BI calculated column numbers to text.

Power bi calculated column percentage

Here we will see an example of Power BI calculated column percentage.

For example, we will use the below table and we will calculate the profit percentage.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Profit percentage = DIVIDE('Product'[Profit],'Product'[CP],0)

• Now click on the calculated column and change the format to a percentage.

This is an example of Power Bi calculated column percentage.

Power bi calculated column percentage of total

Here we will see an example of Power BI calculated column percentage of the total.

For example, we will use the below table and we will calculate the total of Amount 1 based on the product, then we will calculate the percentage of grand total based on the product.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box to calculate the Total Amount based on the product:

Total Amount 1 = CALCULATE(SUM('Order'[Amount 1]), ALLEXCEPT('Order','Order'[Product]))

• Now to calculate the grand total percentage, create a new column, then write the below formula in the formula box:

grand total percentage = DIVIDE('Order'[Amount 1],'Order'[Total Amount 1],0)

• Then change the format to the percentage of the calculated column in Power BI Desktop.

This is an example of Power bi calculated column percentage of total

Power bi calculated column previous row

Here we will see an example of Power BI calculated column previous row.

For example, we will use the below table and we will get the sales value from the previous row.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Calculated Column = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            'Table 9',
            'Table 9'[Code]< EARLIER ( 'Table 9'[Code] )
        ),
        [Code], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, [Sales] )
RETURN
    'Table 9'[Sales] + PreviousValue

• Now you can see the column added to the table, having the previous row value.

Power bi calculated column decimal places

Here we will see an example of Power BI calculated column decimal places.

For example, we will use the below table, and we will use the Profit percentage(calculated column). This column contains the decimal numbers, we will change them into 2 decimal places from 4 decimal places.

In Power BI Desktop select the Column -> in the Column tools under Format box, change the number of decimal placed 4 -> 2.

This is an example of Power BI calculated column decimal places.

Power bi calculated column sum

Here we will see an example of Power Bi calculated column sum.

For example, we will use the below table and we will calculate the total sales.

• In Power BI Desktop, go to the Modelling tab -> select the new column from the ribbon.
• Then write the below formula in the formula box:

Total sales = Sum('Table 9'[Sales])

• Now you can see the column added to the table having total sales.

This is an example of Power BI calculated column Sum.

Power bi calculated column sum by group

Here we will see an example of Power BI calculated column sum by the group.

For example, we will use the below table and we will calculate the total sum by the group.

• In Power BI Desktop, go to the Modelling tab -> select the New column from the ribbon.
• Then write the below formula in the formula box:

Sum of Quantity = CALCULATE(Sum('Table 7'[Quantity]), ALLEXCEPT('Table 7','Table 7'[Category]))

• Now you can see the column having the total sum of quantity based on category.

This is an example of Power BI calculated column sum by category.

Power bi calculated column switch

Here we will see an example of a Power BI calculated column switch.

For example, we will use the below table and if the number of days <= 5 and action <=2 then Green, else if the number of days> 5 and Number of Days <8 and Action<=2 then Yellow, else if the number of Days >=8 and action<=2 then Red, else if the number of Days<=15 and Action<=3 then Blue, else if Number of Days >15 and Action>3 THEN Orange, else Violet.

• In Power BI Desktop, go to the Modelling table and click on the new column from the ribbon.
• Then write the below formula in the formula box:

Column = 
SWITCH (
    TRUE (),
    'Table 8'[No.of Days] < 5
        && 'Table 8'[Action] <= 2, "Green",
    'Table 8'[No.of Days] < 8
        && 'Table 8'[Action] <= 2, "Yellow",
   'Table 8'[No.of Days] >= 8
        && 'Table 8'[Action] <= 2, "Red",
    'Table 8'[No.of Days] <= 15
        && 'Table 8'[Action] <= 3, "blue",
   'Table 8'[No.of Days] >= 15
        && 'Table 8'[Action] > 3, "Orange",
    "Violet"
)

• Now you can see the column added to the table based on the DAX formula.

This is an example of a Power BI calculated column switch statement.

Power bi calculated column selected value

Here we will see an example of Power BI calculated column selected values.

For example, we will use the table below and create 2 categories, Weekend (Sunday and Saturdays) and WeekDays based on days. We will add the column to the slicer, based on the Weekdays and Weekends it will show the Total Amount.

• In Power BI Desktop, go to the Modelling tab -> then click on the New column from the ribbon.
• Then write the below formula in the Formula box:

Selectedvalue = if(MovingAverage[Dateadd 2 days] = "Saturday" || MovingAverage[Dateadd 2 days] = "Sunday", "Weekend", "Weekdays")

• Now you can see the column added to the table has values based on the formula.

• Next in the Power Bi Desktop Report view, select the slicer and add the Selected value column.
• Then add the Card visual and add the Amount column to the visual. By selecting the values in Slicer, you can see the Total Amount in Card visual.

This is an example of a Power BI calculated column selected value.

Power bi calculated column sum if

Here we will see an example of Power BI calculated column sum if.

For example, we will use the below table, and we will sum the amount, if the Value in the SelectedValue column is a Weekend.

• In Power BI Desktop, go to the Modelling tab -> then select the New column from the ribbon.
• Then write the below formula in the formula box:

Weekend Total Amount = SUMX(FILTER(MovingAverage, MovingAverage[Selectedvalue]="Weekend"),MovingAverage[Amount])

• Now you can see the column added to the table having the total Amount based on Weekend value.

This is an example of Power BI calculated column Sum if

Power bi calculated column subtraction

Here we will see Power BI calculated column subtraction.

For example, we will use the below table to calculate the profit by subtracting the sp value from the CP value.

• In Power BI Desktop, go to the Modelling tab -> select the New column icon from the ribbon.
• Then write the below formula in the formula box:

Profit = 'Product'[SP]-'Product'[CP]

• Now you can see the new column added to the table having the Profit value.

This is an example of Power Bi calculated column subtraction.

Power bi calculated column true false

Here we will see an example of Power BI calculated column true false.

For example, we will use the below table, and if the ‘Status on Date’ column contains a ‘Closed’ value, then it returns true or else false.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

True/Falsebased on Status = if(Project[Status on Date]="Closed", TRUE(),FALSE())

• Now, you can see a new column added to the table, having a true/false value based on the formula.

This is an example of Power bi calculated column true false.

Power bi calculated column time difference

Here we will see an example of Power BI calculated column time difference.

For example, we will use the below table and we will add a calculated column to get the hour difference between OrderDateTime and ShipDateTime.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Time diff = DATEDIFF('Table B'[Order DateTime],'Table B'[Ship DateTime],HOUR)

• Now a new column is added to the table having hours value in Power BI

This is an example of Power bi calculated column time difference.

Power BI calculated column use relationship

Here we will see an example of the Power bi calculated column UseRelationship().

For example, we will use the below table Sales and Date table (calculated table), to get the Year value (from the Date table) based on the Order Date and Delivery date.

Sales table

The date table is a calculated table, with the below formula I have created a Calculated Date table in Power BI.

Date = 
ADDCOLUMNS(
    CALENDARAUTO (),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Year", "CY " & YEAR ( [Date] )
)

So both the table have 2 relationships, i.e.:

• Date column ( Date table) -> Order Date (Sales table) (Active relationship)
• Date column (Date table) -> Delivery Date (Sales table) (InActive relationship)

To get the Year from the Date table for the Order date, it is easy to get in the calculated column as well as in measure because the relationship is active so we can use Related () or we can use UseRelationship inside the Calculate() as filter context. The UseRelationsip () lets you awhile change the active and inactive relationship.

OrderYear = RELATED ( 'Date'[Year] )

Order Year = CALCULATE (VALUES ( 'Date'[Year] ),USERELATIONSHIP ( 'Date'[Date], Sales[OrderDate]))

But if you want to get the Delivery year column from the Date table, in the measure we can easily achieve the Delivery year, by using UseRelationship, even if the relationship is inactive, we cannot get the same result in the calculated column because Calculate() performs context transition before UseRelationship () taking into an account. So after the context transition happened, later UseRelationship changed into an active relationship, as a consequence the UseRelationship() is ineffective.

DeliveryYear = CALCULATE (VALUES ( 'Date'[Year] ),USERELATIONSHIP ( Sales[DeliveryDate],'Date'[Date]))

The solution is by removing the Filter on the Date column data or we can use LOOKUPVALUE(), without relying on the relationship.

DeliveryYear = CALCULATE (VALUES ( 'Date'[Year] ),CALCULATETABLE( Sales,
USERELATIONSHIP ( Sales[DeliveryDate],'Date'[Date]), REMOVEFILTERS('Date')
))

DeliveryYear = CALCULATE (VALUES ( 'Date'[Year] ),CALCULATETABLE( Sales,
USERELATIONSHIP ( Sales[DeliveryDate],'Date'[Date]), REMOVEFILTERS('Date')
))

This is an example of a Power BI calculated column use relationship.

Power BI calculated column based on a measure

Here we will see an example of a Power Bi calculated column based on a measure.

For example, we will use the below table and we will create a measure to calculate the Total revenue. Then we will create a calculated column to get the total revenue based on customers.

• In Power BI Desktop, go to the Modelling tab -> click on the New Measure from the ribbon.
• Then write the below formula in the formula box:

Total Revenue = SUM('Table B'[Revenue])

• Next, from the Modeling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

TotalRevenue for Customer = CALCULATE([Total Revenue],
           FILTER(ALL('Table B'),
            'Table B'[Customer No.]=EARLIER('Table B'[Customer No.])
                 )
          )

• Now you can see a new column added to the table with measures in Power BI.

This is an example of a Power BI calculated column based on a measure.

Power bi calculated column based on another column

Here we will see an example of a Power BI calculated column based on another column.

For example, we have two sample tables that are not in a relationship i.e. Table B ( Customer Number and revenue) and Table c (distinct customer number and Customer name). So we will create a calculated column in Table c that will calculate the sum of revenue for the customers.

Table B

Table c

• In Power BI Desktop, go to the Modelling tab -> click on the new column from the ribbon.
• Then write the below formula in the formula box:

Column = 
CALCULATE (
    SUM ( 'Table B'[Revenue] ),
    FILTER ( 'Table B', 'Table B'[Customer No.] = 'Table c'[Customer No.])
)

• Now you can see the calculated column added to the table based on another column.

This is an example of a Power Bi calculated column based on another column

Power bi calculated column between two tables

Here we will see an example of a Power BI calculated column between two tables.

For example, we have below two tables (are in Many to one relationship) i.e. Table B and Table c. We will calculate the Total revenue when both the table are relatable.

Table B

Table C

• In Power BI Desktop, go to the Modelling tab, then click on the New column from the ribbon.
• Then write the below formula in the formula box:

Total revenue = 
CALCULATE ( SUM ( 'Table B'[Revenue] ) )

• Now you can see a new column added to the table having total revenue.

This is an example of a Power BI calculated column between two tables.

power bi calculated column count by group

Here we will see an example of Power BI calculated column count by the group.

For example, we will use the below table, and we will count the number of products in each group.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Count the product each category = COUNTROWS(FILTER('Table 5', 'Table 5'[Product] = EARLIER('Table 5'[Product])))

• Now you can see the column added to the table, having counts value for the product group.

This is an example of Power BI calculated column count by the group.

Power bi calculated column date from DateTime

Here we will see an example of Power Bi calculated column date from DateTime.

For example, we will use the below table, and we will extract the date from the date-time column in Power Bi.

• In Power BI Desktop, go to the Modelling tab -> click on the New column from the ribbon.
• Then write the below formula in the Formula box:

Order Date = FORMAT('Table B'[ Order DateTime], "MM-dd-yyyy")

• Now you can see a column is added to the table having Date value in Power BI.

This is an example of Power BI calculated column date from DateTime.

Power bi calculated column rankx

Here we will see an example of Power BI calculated column rankx.

For example, we will use the below table, and we will rank the count of the volume.

• In Power BI Desktop, go to the Modelling tab-> click on the New column from the ribbon.
• Then write the below formula in the formula box:

Ranking = RANKX(Binned,Binned[Count of volumne],,DESC,Dense)

• Now you can see the ranking column added to the table in Power BI.

This is an example of power bi calculated column rankx.

You may also like the following Power BI tutorials:

• Power bi change color based on value
• Stacked Bar Chart in Power BI
• Power BI divides two columns

In this Power BI Tutorial, we learned all about Dax calculated columns in Power BI with examples. Examples are listed below:

• Power bi calculated column
• Power bi calculated column vs measure
• Power bi calculated column if
• Power bi calculated column aggregate
• Power bi calculated column average
• Power bi calculated column average by category
• Power bi calculated column add two columns
• Power bi calculated column another table
• Power bi calculated column if and
• Power bi calculated column moving average
• Power bi calculated column cumulative sum
• Power bi calculated column count rows
• Power bi calculated column count if
• Power bi calculated column count
• Power bi calculated distinct column count
• Power bi calculated column divide
• Power bi calculated column divided by zero
• Power bi calculated column contains text
• Power bi calculated column concatenate
• Power bi calculated column left
• Power bi calculated column as legend
• Power bi calculated column age
• Power bi calculated column earlier
• Power bi calculated column empty
• Power bi calculated column max value
• Power bi calculated column if an error
• Power bi calculated column from an unrelated table
• Power bi calculated column grand total
• Power bi calculated column in the matrix
• Power bi calculated column index
• Power bi calculated column if null
• Power bi calculated column date format
• Power bi calculated column dateadd
• Power bi calculated column days between two dates
• Power bi calculated column earliest date
• Power bi calculated column latest date
• Power bi calculated column last year
• Power bi calculated column last month
• Power bi calculated column year
• Power bi calculated column month name
• Power bi calculated column week number
• Power bi calculated column month and year
• Power bi calculated column previous month
• Power bi calculated column fiscal year
• Power bi calculated column today’s date
• Power bi calculated column YTD
• Power bi calculated column multiple if statement
• Power bi calculated column multiply
• Power bi calculated column number format
• Power bi calculated column nan
• Power bi calculated column number to text
• Power bi calculated column percentage of total
• Power bi calculated column percentage
• Power bi calculated column previous row
• Power bi calculated column decimal places
• Power bi calculated column sum by group
• Power bi calculated column switch
• Power bi calculated column selected value
• Power bi calculated column sum if
• Power bi calculated column subtraction
• Power bi calculated column true false
• Power bi calculated column time difference
• Power BI calculated column use relationship
• Power BI calculated column based on a measure
• Power bi calculated column based on another column
• Power bi calculated column between two tables
• power bi calculated column count by group
• Power bi calculated column date from DateTime
• Power bi calculated column rankx