Using built-in functions

- [Instructor] There are a lot of people who don't enter into the field of data because they're intimidated by math. It's important to recognize that one of the powers of these tools is that it performs all types of math from basic to complex mathematical computations for us. We don't have to manually create every function we need. The tool provides us a lot of calculations. For example, in this Power BI dashboard, let's take a look at the fields, look at Quantity and UnitPrice and even Discount. Do you see how they have the sigma shape? It's because it recognizes them as numbers, and this means it will automatically aggregate them for us and summarize them. Let me show you what I mean. I'll go ahead and add a table, and I'll go ahead and expand orders. I want to bring in the order ID. I also want to take a look at the product name. Now let me expand this. I want to be able to see as I ad fields in. I'll go ahead and bring in the quantity, and then I'll bring in the unit price. Do you see how it automatically totals the quantity and the unit price? This doesn't make sense to me. The unit price is just the price and the quantity, well, that's the quantity that was ordered for that order ID. So what I'll do is I'll right-click on the quantity and tell it to not summarize. I also go to unit price and tell it not to summarize. I think I would prefer to see unit price over quantity. So what I'll do is I'll just drag that order and change them, perfect. I do want to see the subtotal, and one thing I'm finding here is that I don't have it. I'll build that in my model. I'll go to Transform Data. I want to add it to the order details. I'll go to Add Column, and I'll choose Custom. Okay, I'll go ahead and call the SubTotal, and here I'm using their function builder. I'm going to go ahead and say UnitPrice, by double-clicking, multiplied by Quantity. Tells me I have no syntax errors, which is great. I'll click OK, and now I have my new subtotal. Notice that my default is A, B, C and 1, 2, 3, alphanumeric. I'm going to go ahead and change that to a fixed decimal number, all right? I'll go to Home, Close & Apply, and then I'll bring my subtotal into my table. Now, in this case, I do want this number to total. This makes perfect sense to do that. This is my amount before I apply a discount. Okay, let's take a look at something else Power BI does for us and, in this case, it keeps us from having to write functions. Notice the order date. It's actually got a date icon, and when I hit the little expand, it has a date hierarchy. That's because Power BI assumes that I will probably want to work with year, quarter, month or day. Let me drag my date hierarchy into my model, and I'll put it up by the order ID. Notice, automatically, I get the four individual fields. There are times I do want this and times I don't. In this case, I just want to see the order date. So what I'll do is I'll actually right-click the order date hierarchy and tell it just to show me the order date. If you work as a data analyst, you probably work with pivots and matrix. Remember, that's roses, columns and summary values. Here, I'm going to add a matrix, and I'm going to look at values based on the shipping country. I'll go ahead and add Ship Country to the rows, and I'll grab my subtotal and add it to my values. This lets me see every single country, and it automatically summarizes its subtotal. Now, if I wanted it to be an average, I could right-click and choose Average. If I wanted to show the max of any particular subtotal in a country, I could choose Max. Again, I'm not completing this math. I'm just choosing the right options. I'll go ahead and choose Sum. Another powerful feature of Power BI is the ability to use quick measures. I'll go ahead and click on Quick measures. These are actually measures that are written in DAX. They're freely available for me to use. I can go ahead and hit the dropdown, and I can see options like Aggregate per category, giving me average, variance, max or min. I have different filter scenarios, different time intelligence scenarios, like year-to-date totals, year-over-year change. I also see totals, like running total. Let's do that. I'll choose the running total. I want to work with my subtotal, and I want that running total to be based on the different country. So I'll go to my orders. I'll choose my ship country. I'll go ahead and leave it as ascending, and I can hover over each one of these options to learn more about it. All right, I'll go ahead and click OK. Now I see the DAX behind this particular calculation and on the right-and side, do you notice how I have my subtotal running total? And it has a little calculator shape. Let me go change this to read RT_SubTotal. Okay, and then I want to actually go put this into my matrix, which is going to just drag it underneath my subtotal there. So the running total works by adding each value to each value. So I started with 8,000 approximately. So my starting running total is 8,000 approximately, and now when I go to the next value, Austria at 134, it adds that 8,000 to that 134 and gives me the 142,000. That's the running total. One of the really great things is that I can actually add more variation to this and change my running total. For example, I want to see a running total across the years. So I can actually drag Year into my columns, and then my running total starts over again for each year. These are just simple examples of some of the power of the built-in functionality in Power BI. Just remember, with power comes a great responsibility. That really sounds like the beginning of a superhero movie. I tell people all the time, anyone can make numbers show up, but that does not make them correct. If I could offer you a piece of advice, really think through what you're trying to accomplish with the numbers, consider what functions you might need and then also read about what's available. The more experience you have, the easier the research will be for you but don't worry, you'll always be studying something new.