Join Ron Davis for an in-depth discussion in this video Using mathematical functions, part of Excel Power Pivot DAX in Depth.
- Mathematical functions. Mathematical functions in DAX are very similar to their siblings over in Excel. So pretty much, if you can use Excel mathematical functions, you can use the same thing over in DAX. There's a few things you need to be aware of, which I'll show you just in a second when I switch on over to our development environment. Right now, if you take a look here, I've got the functions demonstrated two ways. At the very top I have the name, "Sum of ExtendedAmount," and then a colon, equals, and then the actual function itself, "SUM," open parentheses, the name of the table, "FactInternetSales," bracket, and then the name of the column, close, close.
Dropping down at the bottom, I'm showing the ROUNDUP function. So here, I have the equals sign, and then "ROUNDUP," open parentheses, the name of the column, "ExtendedAmount," a comma, and then the 1, for how many digits we want it to roll up to. And then we're just closing out the function. So let's switch over to our development environment, and take a look at that. When you're coming through and actually watching this, and if you're following along and doing it yourself, any changes you make, I'd suggest saving the workbook again with a different name, so that you can always get back to the start.
And I also highly encourage you to do just that. That is, to go through and to experiment with the different functions. Remember we're not going to go through them all. Time just doesn't allow. And many of them are very similar. Okay, let's create a calculated field. And again I'm over in the Excel interface. New Calculated Field, and we're going to use the OrderDetails table. And down here we'll insert a function. And I don't need to.
I'm going to use IntelliSense. So I'm going to go with my open parentheses down here, and S - U and you see IntelliSense helps me here. I'm gonna select SUM, and I'm gonna tab over. And it's asking for the column name again. If I hit the bracket down here, and if I select [discount], again as we did before, I'm gonna tab on across, and I'm going to close it. So we've got the SUM down here of [discount], and let's change the name to "Sum Discount." And we'll say OK.
Alright. Now if I go up here to Calculated Fields, and say Manage Calculated Fields, there's Sum Discount. Now if I call Edit, I can come in and I can edit it. Let's Cancel out of that. Let's switch over to our data model. And we're in the order details, and there it is. Now note the position. Sum of discount is underneath the order ID. This reinforces that this is just the calculation area down here, and it does not have to be underneath, here's the discount column that we summed on up.
So here's sum of discount over here. Now let's look at something else. The mathematical functions. Well, let's go back over and, you notice, now we could change this out here, and put in another function right there, but let's go back over to excel. And Calculated Fields. And I'm gonna go Manage Calculated Fields, Sum Discount, and I'm gonna call Edit. And alls I'm gonna do is copy it. Ctrl C, and I'm gonna Cancel.
New, and I'm gonna dump out the equals, because I canceled that. And now we're going to come in here, A - V and you see I'm gonna tab across AVERAGE. Check formula. Again I've got something in here, so "Calculation error," "Column 'discount' cannot be found. "or may not be used." So, oh yes. Let me show you where the error was.
Let's paste it in again, and let's change this out. Trying to show you a trick, and that works. And of course it can't be found. I'm in the wrong table. So let's go over to OrdersDetail, check it, and now I'm gonna call this "Avg Discount." And we'll say OK. I'm gonna close.
What I'm showing you there, is you can copy and paste your code. You really wanna utilize that. You wanna come through and create small functions that you've tested, and then utilize them. So let's go over to Manage, and now there's my Average Discount. Okay. I wanna show you another little thing. We're gonna go over into advanced. And I wanna show you show implicit calculated fields. Let's move off of this so we can see. When I click that, nothing happens. Now an implicit calculated field is one that we create when we're over in the pivot table.
Let me show you. So I'm gonna go home, and I'm going to create a pivot table. It's going to pop up and ask me for a New Worksheet, and that's fine with me. We'll say OK. So now I've got a Pivot Table down here, and let's expand out OrderDetails. And we'll just put a check in Average, and a check in Sum. So now we're, there's our Average, and there's our Sum. Now, if I wanna see quantity, I'm going to check quantity, and it summed it up for us.
So, now what it created is known as an implicit calculated field. That is, I created it here in Excel. Now let's go over to Manage, and now, when I'm in advanced, and I say show implicit calculated fields, and you notice down here, so you can see it, coming and going, that's, so this tells us that that field is an implicit calculated field.
So why did I go through all that? Many people that work in DAX will tell you to never use an implicit calculated field, for various reasons. The reason that I really don't want you to use implicit calculated fields, is because I want you to code them out yourself, and so that you don't run into some of the errors that you can run into with implicit calculated field. Now some people are really militant on it, and some people, it really doesn't much matter.
But I would suggest against using implicit calculated fields. By the way you can't prevent your end users, if they're in the function over there, or if you send them the spreadsheet and they're working in there, and they go ahead and create it, there's nothing you can do about that. So, just be aware of it. Now as I've created that, I'm gonna go back over, and I'm going to save this. Save as, and I'm going to save this as, "Mathematical Functions End." Just so we've got it.
And I suggest, when you're coming through, if you wanna create some more, do some more work with mathematical functions, and you do that and go through and save it, just as I did, so that you have a clean one to start with.
- Exploring DAX syntax and data types
- Using functions
- Understanding evaluation contexts
- Using CALCULATE to change evaluation context
- Working with dates
- Utilizing many-to-many relationships
- Implementing banding