While many database professionals are proficient in math, I’ve met a few who break out in a cold sweat when I ask them about it.
As with most things I teach, I always try to start simple. You’re a database administrator, not a financial modeller. It’s the same with statistics; once you’ve mastered the fundamentals, you can ignore the rest until you really need it.
So, to make things a little less scary, and especially if you’re doing a lot of work with reports, here are a couple of things that might help:
| Function | In Excel | In Power Bi (DAX) | Notes |
| Sum (Adding Up) | Cell + Cell or =Sum() | Sum() | This will combine values. If you do this with cells, all of the mathematical operators (+,-,/,*) will work. |
| Mean Average | =Average() | Average() | This will yield the mean average. (Total all the numbers and divide by the number count) |
| Mode Average | =Mode() | It’s complicated | In Power Bi, you must calculate the frequency of the numbers that appear to take the one at the top. The Mode Average is the average based on how many times a value appears in a list. |
| Median Average | =Median() | =Median() | The median average is the number in the middle of the range; it is similar to the mean but differs slightly. |
| Percentage | =cell*x% Or =(small number / big number)*100 | =DIVIDE( small number, big number, 0) *The zero just removes the possible errors. | If you wanted to know what a percentage of a number is, for example, the GiftAid amount on a donation, you could multiply the donation amount by 25%. If you wanted to know how much Individual Giving income there was in comparison to the rest of the organisation, you would need to total (sum) the income for Individual Giving, sum the income for the organisation, and then divide the individual giving income by the total income and multiply the result by 100. Essentially the way I remember it is small number divided by big number multiplied by 100. |
There are many resources on the internet that can assist you with these functions and provide a more detailed breakdown, but hopefully this means that some of you who used to break out in a cold sweat now have a little more assistance.
As with previous posts, these are not exhaustive. As with the post on Excel Functions, I’m not here to rewrite the Microsoft help files; rather, I’m here to point you in the right direction.