DA-100 Microsoft Power BI – Part 3 Level 3 – Mathematical functions

1. Rounding functions

In this section of the course, I Want to Talk about mathematical Functions and Want to address a Problem that we had to 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 1 August and we’re talking about product key five to eight. So that just Gives us six rows. The sales amount is 499 for each of these items. So 499 and the six items gives us 29 pound. 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 it by 1. 6. So that comes to seven Pound 98 or that’s what it shows. But Seven Pound 98 times Six is not 47 pound 90, it is in fact 47 Pound 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 Seven dollars. Ninety eight point four.

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 a true value based on several decimal places, but It might be that you actually want It to round to the nearest end. 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 one difference, the ISO dot 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 we’re talking about int and Trunk. So what Int does is it rounds down to the nearest Integer. So the int of 3. 6 is free. So The Int of 3. 99 is Free but the int of -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 trunk of 3. 6 is free but trunk of -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 trunk are different. Then we have got ceiling and it’s ISO equivalent.

What ISO means. It’s an international organization of standardization. What they’re trying to do is Say, well, these are 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’s the difference between the two? But 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 a dollar 42 to nearest $0. 05 going up is a dollar 45. And as I said it’s the same for the ISO ceiling. For floor it goes down to the nearest significance.

So one dot 42, which is the nearest five cents, a dollar forty two is a dollar forty. Now the function M round, 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 M round of a dollar 42 to nose to, but M round of a dollar 43 is a dollar 45. So in this way you can round a number up and down to 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, round down and round up 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 a dollar 42 to the nearest 10th, then we’ll be putting comma one and round rounds either down or up. Round down will always round down and round up will always round up. So M round gives you a bit more flexibility in that you can say well I don’t want it the nearest 10th, I want it to nearest fifth.

Now similarly we can go to the nearest 100 for instance in using the round functions. So if I was rounding to the nearest 100, well if one is a 10th then two is 100th, zero is the nearest whole unit, minus one is the nearest ten and minus two is the nearest 100. So you can see that there is a lot of similarity between round and M round. Maybe M round has more flexibility, but most Excel users I think would be using the round functions by default.

So if we round that to nearest minus two digits, there we go, that rounds to the 100, whereas rounding up here would have to use a minus two would round to the nearest 100 and making 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 use 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 the nearest 100th, which is the nearest cent. So now instead of $7. 98. 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 nearest 100 or nearest thousand or nearest million. So these are your functions for mass and trig rounding. All of them operate exactly the same way as they do in Excel with just one edition, the ISO C link. So these are your rounding function.

2. Division functions – MOD and QUOTIENT

Now, closely related to these rounding functions, especially a rounding function like int or trunk are the mod and quartant 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 fours? 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 into five divided by two which would of course give me two. Now, quartant is very similar to this except instead of using a 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 five comma two, which of course is two in itself. Now, very little use to be honest to with quarters I quite most of the time just use the int. It’s more flexible. I can use divides, but I can also use it for 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 have the quartet. It would be mod open bracket five, comma two. So if calculate that, then it is remainder one. Similarly, if it was mod ten, comma four, then that’s remainder 220, comma eight, that’s remainder four and so on.

So of these functions the most useful I think is the mod. So suppose you had the role number and you wanted to divide it into roles 1234 and then repeat 1234-1234. Well, you could use the mod for that. Divide the raw number into four and then you’ll 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 results to go from 0123 to 1234. So of these, mod is the most useful. It gets the remainder. But where there’s a mod, there’s also quartant, which gives you the whole numbers. But personally, I just use int for the.

3. 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 free, but the ABS of minus three is plus three. Sign points out whether in number is positive, negative or zero. So the sine of four being a positive number gives me plus one. The sine of minus four being a negative number gives me minus one and the sine of zero will give me zero. So it only gives me three answers, plus 10 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 visualization here that tells me the amount that is sold in each year. But I want to know whether that is for smaller items, more inexpensive items, more costly items, or items of a specific quantity value. And I’m going to call that value £4. 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 499. So I’m going to create a new calculated column and let’s call it Compare with 499. So if the sales amount is less than 499, then we’ll call it less. If the sales amount is greater than 499, 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 499, there’s a 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 visualization. So it’s called Compare with 499. So it’s added in and you can see we only started diversifying into smaller products in 2007 and it’s not made that much of a big splash. But that gives us the information we need and we could probably change it now from 499 to say, well, let’s go look at £10, let’s look at £20, and so on. But let’s have a look at a similar way of getting this, but a lot shorter, using switch and using sign. So what we’re going to do is calculate how much more than this particular item is 499.

So obviously, where it is 499, it’s a zero value. Where it’s more than, 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 more. 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 to do debugger. So it gives us exactly the same figures, obviously, but for me, a lot simpler. So I use sign quite a lot with switch.

Now, suppose I wanted to say, okay, maybe not 499 itself, but is it between five or $5 and $11, let’s say? Well, what I’m really asking is, is it within $3 of $8, so it can be as low as five or as high as eleven. 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 we’ve got the same figure minus three and three, albeit that there was a minus sign in the first one.

So what we want is the absolute value of this. So the absolute value of this, as you can see for this value is £3. 01 pence. So it is 499. So 499 minus eight, is -3. 1 it gives us plus 3. 1 when we give the absolute figure. So we start off with -3. 4101, we now have plus 3. 1, 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 using this is I’ve not got to use an and I’ve not got to do a calculation again, it would be if and sales amount greater than, equal to five, sales amount less than or equal to eleven. That’s quite a lot of words.

And I’m comparing the same thing twice, whereas here I’m just 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.

4. Exponential functions

In this video, I’d like to talk about exponential functions. So we’re not talking about doubling or tripling a value, but cubing squaring. So going to the power two, going to the power three. So that is why you can use the power function. But then you 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’ll use most often, except I don’t.

And 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 free to the power of two. So three times three is nine, free comma three is free cubed three times three times three, which is 27 and similarly square rooting. So the square root of nine is free because three times three is nine. So why don’t I use these functions? Because on my keyboard shift, six is a carrot or carrot. And that for me, does the job just as well and a lot more succinctly.

So free carrot two is nine, free carrot, 327. Similarly, if I want a square root three carata half, which is zero five or comma five, depending on your locale, so that’s what I use instead. Let’s have a look at the other functions. EXP, that is the constant E 2. 71, eight, et cetera. The Ln function is the opposite of the EXP function. So if the EXP function of one is 2. 78, 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 ten or the power five but I’ve never used those and probably never will outside of any statistics or maths requirements that I never have. And 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 carry function instead.

5. Other functions

So let’s have a look at the rest of the mathematical and trigonometric functions. So, trigonometry, you’ve got all your favorites like Cos signs and Tans and the reverse combine or Combine and combine. A. Let’s suppose that you have four socks and you’re pulling out two of them. So let’s say your socks are called ABCD, or you’ve got six possibilities. You could pull out AB, Acad, BCBD and CD. Now, it doesn’t matter that you might pull out B first, and then A, you’ve got six possibilities of what you’ll end up with comb A. Well, what happens if you return the first sock into the draw?

Then you’ve got four additional possibilities a BB, CCD. So, not often used. As you might expect, 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, an odd. They round up to the nearest even or odd number factorial. So the factorial of five is one times wo, times three, times four, times five. GCD and LCM Least common multiple. 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 31415, et cetera. Product and product x. They multiply numbers in a column or in a table and rand and rand between. They’re probably not that useful in a model. I suppose if you wanted to say I want a third of rows. So I’m going to use random 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 mass and trigonometric functions. So these are some of the other mass 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 web page which will give you all the information, including syntax and usages that you might want about it.