Creating a Calculated Column.

Calculated column is an additional column of information which we can put into our data set by writing a formula.

Formula has a number of components all of which must be written correctly so that the calculation can be performed. This is known as the syntax in Power BI the first part of the formula syntax is the name we want to give to the new column, followed by an equal sign. So if we're creating a new column, which will calculate the amount of commission a Trader will receive on a trade we might start it with commission value equals.

One of the most common types of formula is a mathematical equation, which uses common operators such as add ,subtract, multiply or divide.

If we're adding this type of formula, then the syntax requires us to use symbols to indicate which operator is to be used. So for example, if we want to multiply we'll use an asterisk or if we divide we'll use a forward slash.

Finally, we need some values to calculate with we often get these values in existing fields if we're referencing a field name in a formula. It must be written in the following way the name of the table, which the field belongs to, followed by the field name itself, and it must be in square brackets.

So if we're using a field called stock cash value in a table called trades it would be written in the following way.

Here we have an example of how all that is brought together a column which stores the stock cash value, another column which tells us what the commission rate the Traders are getting on that value is, and so we may want to find out what the commission value is for each trade. That would require a multiplication of the stock cash value by the trader commission rate, so our formula syntax would be commission value equals the trade table stock cash value field multiplied by the trades table Trader commission field. Let's do a workout and see how to create such a calculated column in Power BI.

So I'm just going to get some data from my Excel workbook, I'll take module 6 lesson 1a workout.

And I'll load a table called trades into my report.

So when we create a calculated column we do it from the data view. So I'm just using the icon down the left-hand side here to switch to data view and I can see all of the columns in my table and I want to add a new one in so I'm going to use the ribbon, the table tools ribbon here, to click on new column.

And I'll get a new column, it's always added in at the end, we can see it here and I have a formula bar that allows me to write in the formula. So I can see that it suggested a column name for me, so I'm going to replace that, I want to call it something different. I'm going to call it commission rate.

And follow that with an equal sign.

So I then need to build my formula and I want to take the value from the stock cash value field first of all, so if I start typing it what happens is we get an auto complete. So this is a list of current field names some functions and I can easily select what I want from here. It's much easier than trying to actually type it in the more I type the less I have to select from. I can see here I've got the field that I need and I can use my arrow keys and then tap it in or i can just click on it and it will be inserted for me and we can see the syntax, the field name in square brackets, and the actual table name in front of that. Now I know I want to multiply so I'm using an asterix there to indicate multiplication and I want to multiply it by the value in the trader commission column. So again, if I just start typing Trader commission and I can see here's the field I want so I'll use during arrow and tab and it goes and what I'll do is now I press return or enter.

And that will check the Syntax for me. And as long as the syntax is correct, it will then give me the result of my formula. I now I have a new field which I can then use in my report. If I click to report view, I'll see it sits here alongside all the other fields slightly different icon just indicate that it's a calculation not an ordinary field value.