Look Who it Was Random video

Excel - Calculated Columns in Excel Power Pivot - Episode 1260

2,162 views · Published 6 October 2010 · 4:33 · Indexed 25 September 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Power Pivot Calculated Columns

Welcome to the MrExcel netcast. In this episode, we will be diving into Chapter 6 of Power Pivot For The Data Analyst, where we will be exploring the world of calculated columns. I'm your host, Bill Jelen, and I'm excited to show you how to use the DAX language right in the Power Pivot Grid to add new calculated columns.

But before we jump into that, let's take a quick look at some great Date Handling functions in Excel. These functions make it incredibly easy to break down a date into its year, month, and day components. For example, the =YEAR() function will give us the year of a given date, while =MONTH() will give us the month, and =DAY() will give us the day. However, these functions return numbers, which may not always be the desired format. That's where the =TEXT() function comes in. By using this function, we can format the date to appear exactly how we want it, whether it's spelled out or in a specific format.

Now, let's see how we can apply this knowledge in the Power Pivot window. We have a Date field and we want to add a new calculated column. We simply start with an equal sign and type the function we want to use, such as =YEAR(). As soon as we start typing, Power Pivot offers us a few choices, and we can use the Tab key to select the one we want. Then, we just need to specify which field we want to use, either by typing the field name or clicking on it with the mouse. We can do the same for the =MONTH() and =DAY() functions, and just like in Excel, we can use the =TEXT() function to format the date in a specific way.

One thing to note is that the =TEXT() function is spelled as =FORMAT() in Power Pivot, so keep that in mind when using it. And just like in Excel, we can change the heading of our calculated columns by right-clicking on them and selecting "Rename Column". Chapter 6 of Power Pivot For The Data Analyst has a complete list of all the DAX functions and examples of how to use them, so be sure to check it out for more in-depth information.

I hope this episode has given you a better understanding of how to use calculated columns in Power Pivot using the DAX language. Stay tuned for more netcasts from MrExcel, and don't forget to subscribe to our channel for more Excel tips and tricks. Thanks for watching!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Calculated Columns in Power Pivot
(00:15) Overview of DAX Language in Power Pivot
(00:30) Using Built-in Functions for Year, Month, and Day
(00:56) Using the Text Function for Custom Formatting
(01:06) Examples of Different Date Formats
(01:29) Creative Uses for Text Function
(02:05) Applying Excel Knowledge to Power Pivot
(02:15) Adding Calculated Columns in Power Pivot
(02:57) Changing Column Headings
(03:16) Differences Between Excel and Power Pivot Functions
(03:26) Using the Format Function for Text Formatting
(03:55) Changing Column Headings Again
(04:05) DAX Functions in Chapter 6
(04:15) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #pivottable #pivot_table #excelpivot #excelpivottablestutorial #powerpivot #DAX #getpivotdata #pivotchart

This video answers these common search terms:
how to create dax measure in excel power pivot
what is a measure used for in power pivot excel
excel power pivot calculated columns
adding calculated fields to power pivot
creating custom columns in power pivot
dax formula for calculated columns in excel
power pivot calculated column examples

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152941/


Today, in Episode #1260, Bill investigates Calculated Columns in the PowerPivot grid using DAX. Also, tips to Format your data for a more visually aesthetic result using PowerPivot and Excel 2010 ... from Bill Jelen a.k.a."MrExcel".

More from this channel