PL-300 – Section 26: Part 3 Level 4 – Mathematical Functions

193. Introduction to Mathematical Functions

Hello everyone and congratulations for making it through to this section.

In this section, we’ll be having a look at the Mathematical functions within the DAX language. They operate very similarly to those in Excel.

If you already know how to use rounding functions in Excel, then you might want to skip this section.

If you know how to use rounding functions in the M language from earlier on this in this course, then you might want to watch the next video, as the DAX ROUND functions are slightly different to their M equivalents. After that, you might want to go to the next section.

However, if you want to know about the mathematical functions in DAX, please join me in the next few videos.

194. Rounding Functions

In this section of the course, I want to talk about mathematical functions and want to address a problem that we had a look at a few sections ago.

Now, the sales amount, I’ve done a filter on this so that we’re just talking about the first of August, and we’re talking about product key 528, so that just gives us six rows. The sales amount is $4.99 for each of these items. So, $4.99 for the six items gives us $29.94. That’s good, that’s exactly what we want. But the sales amount in USD, in United States dollars, we’ve used a formula, which was, give me the sales amount in Great British pounds and multiply by 1.6. So that comes to $7.98, or that’s what it shows, but $7.98 times six is not $47.90. It is in fact $47.88, and the reason why there’s this discrepancy is because there is a rounding problem. So, if we go down to sales amount USD, we’ll see the actual amount is not $7.98 but $7.98 point 4, and so now you can see why it adds up differently.

So, in this video we’re going to have a look at the rounding functions and see if we can cure this problem. Of course, this may be exactly the value that you want, and you want the total to be at true value based on several decimal places. But it might be that you actually want it to round to the nearest cent. So, these are the rounding functions that we’ve got, and they’re probably quite familiar to you, because they are Excel functions with, perhaps, once difference, the ISO.CEILING. But that’s just the same as the CEILING function albeit that the second argument is optional. You can see these hard brackets.

Now, I want to start with talking about INT and TRUNC. So, what INT does is it rounds down to the nearest integer. So, the INT of 3.6 is three. So, the INT of 3.99 is three but the INT of minus 3.6 is minus four. It rounds down to the nearest integer. Truncate does a similar function except instead of rounding down, it goes towards zero. So, for positive numbers, it gives you exactly the same value. So, TRUNC of 3.6 is three. But TRUNC of minus 3.6 when you get rid of everything after the decimal point, after the dot, after the period, is not minus four, but minus three. So that is one of the ways that INT and TRUNC are different.

Then, we have got CEILING and it’s ISO equivalent. What ISO means it’s International Organisation of Standardisation. What they tried to do is say what these or how these functions should behave. CEILING doesn’t quite fit into the ISO because you need to put in the second argument significance. But that’s the only difference. So CEILING and FLOOR, what the difference between the two? Well, it’s like a room. FLOOR is down, CEILING is up. But we can say to what multiple of significance we want to go to? So, for instance, the CEILING of let’s say $1.42 to the nearest five cents going up, is $1.45. And as I’ve said, it’s the same for the ISO.CEILING. For FLOOR, it goes down to the nearest significance. So 1.42 to the nearest five cents, $1.42, is $1.40.

Now, the function MROUND, even though it contains the round of these functions, is more closely aligned to CEILING and FLOOR in that it operates exactly the same except it decides whether to go up or down. So, the MROUND of $1.42 to the nearest five cents is $1.40. But MROUND of $1.43 is $1.45. So in this way, you can round a number up and down to the nearest five cents to the nearest unit or indeed, you could have it to the nearest 100 if you so wish. Or the nearest 200. Same for CEILING, same for FLOOR, except those go up or down. So, you can see that when it says significance for CEILING and FLOOR, what they really mean is what is the nearest multiple?

Now, the ROUND functions, ROUND, ROUNDDOWN, and ROUNDUP, are akin to CEILING and FLOOR. Except that it doesn’t say, “I want you to round to the nearest five cents or the nearest 200.” instead, it rounds to a specified number of digits. So, if we round $1.42 to the nearest tenth, then we’ll be putting comma one. And ROUND rounds either down or up. ROUNDDOWN will always round down. And ROUNDUP will always round up. So, MROUND gives you a bit more flexibility in that you can say, “Well, I don’t want it to the nearest tenth, I want it to the nearest fifth.”

Now, similarly, we can go to the nearest hundredth, for instance, in using the ROUND functions. So, if I was rounding to the nearest hundred, well, if one is a tenth and two is a hundredth, zero is the nearest whole unit, minus one is the nearest 10, and minus two is the nearest 100. So, you can see that there is a lot of similarity between ROUND and MROUND. Maybe, MROUND has more flexibility, but most Excel users, I think, would be using the ROUND functions by default. So, if we round that to the nearest minus two digits, there we go. That rounds to the hundred where I was rounding up here. I have to use a minus two, would round to the nearest hundred and make it 12,400. So, this answers a question that we were looking at earlier.

How can we change this formula so that it’s rounded to the nearest two decimal places? So, we can choose almost any of those formulas. But I’m going to use the ROUND function and I’m going to round it to the nearest two digits, which is to the nearest hundredth, which is to the nearest cent. So, now, instead of $7.98 point 4, we now have $7.98 exactly, so our totals now add up. So, these rounding functions allow us to get rid of things that we don’t want.

Perhaps, we don’t want to show it, and we want the totals to be based on what we show, or, maybe, we have another reason for rounding. We just want to see what this figure is to the nearest hundred or nearest thousand or nearest million.

So, these are your functions for maths and trig rounding. All of them operate exactly the same way as they do in Excel, with just one addition, the ISO.CEILING. So, these are your rounding functions.

195. Division Functions – MOD and QUOTIENT

Now, closely related to these rounding functions, especially a rounding function like INT or TRUNC are the MOD and QUOTIENT functions.

Now, if I was to go to an eight- or nine-year-old, and ask them what five divided by two is, the answer might be two and a half, but equally, five divided by two could be two remainder one.

And similarly, if I asked what ten divided by four is, I might get two remainder two. So, they’re the same answer, two and a half, but the remainder is different because we’re dividing by a different value.

Now, suppose I wanted to get the two, the whole units. Well, I could use the INT function. So, INT five divided by two, which will of course give me two.

Now, QUOTIENT is very similar to this, except instead of using the divide sign, you would separate it out into the numerator, that’s the top bit of the fraction, and the denominator, the bottom bit. So, it would be 5,2 which of course is two in itself.

Now, very little use to be honest, with QUOTIENT, because I quite often most of the time just use the INT. It’s more flexible, I can use divides, but I can also use other things. However, the MOD returns the remainder, and that is more useful. So, if I was to use MOD, I would have it exactly the same way as I had the QUOTIENT. It would be MOD open bracket five comma two. So calculate that, then you see it’s remainder one. Similarly, if it was MOD ten comma four, then that’s remainder two, 20,8, that’s remainder four, and so on.

So, of these functions, the most useful I think is the MOD. So, suppose, you had the raw number and you wanted to divide it into rows one, two, three, four and then repeat. One, two, three, four, one, two, three, four. Well, you could use the MOD for that. Divide the raw number into four, and then you get the numbers one, two, three, and zero which you could assign to four or you could just add one to it so you could have MOD if it was raw number four, you could just add one to the results to go from zero, one, two, three to one, two, three, four.

So, of these, MOD is the most useful. It gets the remainder, but where there’s a MOD there’s also a QUOTIENT, which gives you the whole numbers but personally I just use INT for that.

196. SIGN (and Use with SWITCH) and ABS

In this video, we will be looking at the ABS and the SIGN functions. Now, these are great for dealing with negative numbers.

What ABS does, is removes the negative sign from a number. So, for instance the ABS of three is three, but the ABS of minus three is plus three.

SIGN points out whether a number is positive, negative, or zero. So, the SIGN of four being a positive number gives me plus one. The SIGN of minus four being a negative number gives me minus one and the SIGN of zero will give me zero. So, it only gives me three answers: plus one, zero, or minus one.

So, let’s see how we can use these two functions in association with a switch function in our model. So, I’ve got a small visualisation here. That tells me the amount that is sold in each year. But I want to know whether that is for small items, I more inexpensive items. Costlier items, or items of a specific quant value. And I’m going to call that value four pounds 99. As I know there are a lot of four pound 99 items in this data. So, what I need to do is find out if it’s less, same, or more than 4.99. So, I am going to create a new calculated column. And let’s call it Compare Weigh 4.99. So, if the sales amount is less than 4.99, then we’ll call it less. If the sales amount is greater than 4.99, then we’ll call it more. If not, it’s the same. So, this is one way of doing it. And you can see why it is the same. It is 4.99. That’s the sales amount. And then when it’s less, it’s less and when it’s more, it’s more.

So, we can introduce this column into our visualisation. So, it’s called Compare Before 4.99. So, let’s add it in. And you can see we only started diversifying into small products in 2007. And its not made that much of a big splash. But that gives us the information we need and we can probably change it now from 4.99 to say we’ll let’s look at 10 pounds, let’s look at 20 pounds and so on. But let’s have a look at a similar way of getting this but it’s a lot shorter using switch and using SIGN. So, what I’m going to do is calculate how much more than this particular item is 4.99 So, obviously where it is 4.99 it’s a zero value. Where it’s more, then it’s a positive value and where it’s less we have a negative value. So, we take that value and we look at the SIGN of it. Is it negative one, is it zero, is it one? So, where it is minus one, it’s less. Where it’s zero, then it’s the same. And when it’s one, then it’s one. For me, this is a lot simpler. I don’t have to have multiple if statements asking the amount and it’s a lot easier so you do boga. So, it gives us exactly the same figures obviously, but it for me a lot simpler. So, I use SIGN quite a lot with switch.

Now, suppose, I wanted to say, okay, maybe, not 4.99 itself but is it between five or five dollars and 11 dollars let’s say. Well, what I’m really asking is he within three dollars of eight dollars. So, it can be as low as five or as high as 11. So, what I’m doing there is saying, okay if the sales amount minus eight is greater or equal to minus three and if it’s less than or equal to three. So, I’ve got the same figure. Minus three and three all bathed that there was a minus sign in the first one. So, what we’ll what is the absolute value of this. So, the absolute value of this, as you can see for this value is three pounds one pence. So, it is 4.99. So 4.99 minus eight is minus 3.01. It gives us plus 3.01 when we give the absolute figure. So, we start of at minus 3.41 01, we now have plus 3.01. So, now we can say, if that figure is less than or equal to three then it’s close enough. If not, it’s outside of this range.

So, the advantage of this is that I’ve not got to use a hand, I’ve not got to do a calculation again. It would be if and sales amount less than or equal to five, sales amount less than or equal to 11. That’s quite of words and I’m comparing the same thing twice. Whereas here, I’m just going having the sales amount just being compared one time.

So, ABS and SIGN these are great for dealing with negative numbers. So, they are good for finding out if a number is below, or equal to, or above another number or whether it is within a certain range.

197. Exponential Functions

In this video, I’d like to talk about exponential functions. So, I’m not talking about doubling or tripling the value, but cubing, squaring, so going to the power of two, going to the power of three. So, that is where you can use the power function. But then you’ve also got square rooting, which is the same as going to the power of a half.

Now, out of all of these functions that are on the screen, these two are the ones which I would use most often, except I don’t. In fact, I really don’t use any of these functions, and I’ll tell you why I don’t use these two functions. You can say power three comma two, so that is three to the power of two, so three times three is nine. Three comma three is three cubed, three times three times three, which is 27. And similarly, square rooting. So, the square root of nine is three, because three times three is nine.

So, why don’t I use these functions? Because on my keyboard Shift + six is a carry or caret, and that, for me, does the job just as well and a lot more succinctly. So, three caret two is nine, three caret three, 27. Similarly, if I want a square root, three caret a half, which is point five or comma five, depending on your locale. So that’s what I use instead.

Let’s look at the other functions. EXP, that is the constant e, 2.718 et cetera.

The LN function is the opposite of the EXP function, so if the EXP function of one is 2.718, then LN of 2.718 is a number which is roughly one.

And we’ve also got log giving non-natural logarithms, so if you wanted a log of the power 10 or the power of five, but I’ve never those, and, probably, never will, outside of a statistics or maths requirements that I never have.

And then there’s this one, square root of a number multiplied by pi. Again, not something I’ve used.

So out of these functions, these two functions would be the ones that I would use the most often, except I don’t. I use the carry or caret function instead.

198. Other Functions

So, let’s have a look at the rest of the mathematical and trigonometric functions. So, trigonometry; you’ve got all your favourites, like cos, sins, and tans and the reverse.

Combine or COMBIN and COMBINA. Let’s say, you’ve got four socks in a drawer and you’re pulling out two socks at random, how many different combinations are there? Well, it’s four times three being 12. And combine A or COMBINA is what would happen if you put the sock back in, so you’ve drawn a sock, put it back in, and you draw a second sock. So, you could have a repeat of the socks, so that’s four times four, 16.

A lot of this list is fairly esoteric and I don’t really use them much. DEGREES, just like radians, we’re back to trigonometry.

DIVIDE. Well, that’s the same as going, equals five divided by two, but there is a difference. You can put in an alternative result. So, for instance, if you’re doing five divided by zero, which, obviously, gives an error. Well, what do you want instead of the error? So, you could have a blank or you could have an alternate number.

Even and odd, they round up to the nearest even or odd number.

Factorial. So, the factorial of five is one times two times three times four times five.

GCD and LCM, least common multiples, what’s the least common multiple of six and eight? Well, that’s 24 because that’s the smallest number which divides evenly into both six and eight, smaller numbers like 16 don’t do both. And the greatest common divider. What is the largest number that divides into 40 and 48? Well, the answer is eight. And that is the largest number that does both.

PI. Well, that’s just a figure of 3.1415 etc.

PRODUCT and PRODUCTX. They multiply numbers in a column or in a table.

And RAND and RANDBETWEEN. They’re probably not that useful in a model. I suppose if you wanted to say, “I want a third of rows,” so going to use RANDBETWEEN, to pick a number between one and three for each row and then I’m going to filter only those items which are number one, that’s one possible use but, you’ve also got the sample function. Now, the sample function works in measures, whereas this could work across the entire table in a column, but I’ve never actually used any of this, in Power BI.

So, these are some of the other maths and trigonometric functions. As usual if you want any information on any of these, just click on the hyperlink and that will take you to a webpage which will give you all the information, including syntax and usages that you might want about it.

• Category: Uncategorized