Now, sometimes we need more information than we actually have in our datasets. For instance, it would be really nice if I could see the Gross or Total Sales here. Now, we actually do have the data here that we could use to calculate the Total Sales: we have the MSRP and have the Sales Quantity. So let's calculate these together and create some new data. To create a new column of data, we just click New Column on the Home ribbon.

From here we're gonna tell Power BI how to calculate this. So I'm going to click on a spot in our new column, and from here we can just enter the calculation that will calculate Total Sales. Just like formulas in Excel, we start with the equal sign. But unlike Excel, you will not see the calculation being entered inside the column. But Power BI does have a formula bar, and this is where your eyes should go when you're writing your formulas. We should know that the syntax used when referring to data is a bit different in Power BI.

First of all, Power BI doesn't care at all about cells, it only cares about columns and the tables that those columns come from. The syntax used to denote a table is a single quote, or apostrophe. So if I type a single quote, Power BI assumes I'm trying to reference a table and it gives me a list of tables I can choose from. And it is also showing me a list of columns in that table. I can select any one of those columns by double-clicking with my mouse or I can use my keyboard arrows to highlight the column that I want and then tap the TAB key on my keyboard to insert it into my formula.

Okay, and now I'll insert my universal math symbol for multiplication and then reference the other column I want the MSRP multiplied by, which is the Sales Quantity. And I'll click Enter. And now I have my Gross Sales numbers. Let's change the column's title to be more descriptive. I can right-click on the column and choose rename. Or, did you notice that the title of the column was actually written before the formula in the formula bar? We can actually name our column at the same time that we are creating our formula. I'll just type my column name and hit Enter and it's renamed.

We just created what is called a Calculated Column, which is an additional column of data that we added to our table, which was not part of the original dataset. We can also create what is called a measure, which is a custom calculation you can create for your dataset. There are a million and one reasons to create a measure, but we're gonna look at one very simple scenario: calculating next year's target sales.

At our company we hope to have a 3% growth in sales each year. So when the end of the year comes around and we have our big meeting, we always use some visuals to show how we did this year and what we aim for next year. So we wanna create a measure that will be able to calculate what our new target is for next year. Measures sit on the sidelines so-to-say. They are not actually part of the table, but they sit on the side and can be used with any data in the table, or related to the table.

To get started creating a Measure, we will click "New Measure." But before we do that, let's make sure that we have the correct table selected where we want this measure to go. Okay, looks good. And now for our new measure. And let's name this "Next Year Target." And this measure will have to do two different things: It needs to add up all the sales this year and then add 3%. So to add up all the Sales, we can use our old friend, the SUM function. If you are familiar with functions in Excel, then this will feel like home. SUM just adds up a bunch of numbers, which is perfect because we wanna add up all the sales.

Just like Excel functions, we need to open a parens, and then inside the parens goes the data we want to add. In this case, it's the Gross Sales column that we just created. And we know how to denote a table and a column now. So I'll start with my single quote, or apostrophe, and then insert my table and column in this function. Looks good so far, let's close that up. And then multiply the result by 1.03. Let's hit Enter, see if we get any alerts.

Okay, no alerts. Let's use this in a visual to make sure it's working. And it worked beautifully. Now, there are a couple things to note from this section. The calculations in Power BI, like the SUM we just used, are actually called DAX, or Data Analysis Expressions. And the simplest and most recognizable ones are our old friends from Excel: SUM, AVERAGE, MIN, MAX, COUNT, et cetera. These types of calculations that take a whole bunch of numbers and distill them down to a single value that represents the group are called Aggregations. And the results of Aggregations are what we call an Aggregate. These are both terms often used in this area, so let's keep our ears out for them.

Now that we know how to build some basic Aggregation DAX, let's see how to use these calculations on an even bigger scale by sharing with others.