Up to this point in the course, I've introduced you to the switching functions for conditional calculations,
the end in all functions for logical determinations, and I've touched on some supporting functions
like the date diff divide, ceiling and floor options.
Amidst it all, while we've used some aggregation functions, I haven't really talked about them very
much for this lesson.
I'm basically going to cover what aggregation functions are, talk briefly about them and then list
and describe each one.
No examples here today.
So what is an aggregation function?
Generally speaking, aggregation functions work with a list of inputs and then generate one result,
I think some you put in a list of numbers and then you get the total.
DACs generally supports aggregation functions in the standard functions and then the functions that
we discussed a few lessons back.
For the purposes of this lesson, I'm going to focus on the standard functions.
But note that all of these have X versions that you can use.
Let's start with some basic occupations, basic aggregations do those things like some count and so
on.
The sum function takes a series of numbers, add them together and returns to total.
We've used this a couple times.
In this course, the count functions pretty similar, except it takes a series of numbers, counts them,
and then returns that total in alternative form called the count.
A function will count any item, whether it's text or numbers, and then return that total.
Finally, there's a product function that'll take a series of numbers, multiply them together and return
the total product of all of the numbers together.
Also, within the basic aggregations you call the maximum functions basic, the max function looks through
a series of numbers, finds the largest value and will return that number on the opposite end of the
spectrum, the minimum function.
The men will take a series of numbers, find the smallest value and return it.
DAX has a series of statistical aggregation functions to.
The most common is the average function, which takes a series of numbers, calculates the linear mean
and returns that value the linear mean would be the sum of all of the numbers divided by the count of
those numbers.
The median function takes a series of numbers and finds the median or fiftieth percentile of them and
returns that value.
So if you had a list of three numbers one, two and 10, while your average would be 13 divided by three,
your median would be two, since that's the number at the fiftieth percentile.
The Jiemin function takes a series of numbers and calculates the geometric mean and returns that value,
now that geometric mean will be the product of all of the numbers in your list.
Raise the power of one divided by the count of those numbers.
So if you had three, four and five, it would take three times four times five and raise that to the
power of one over three.
The Vahdat P and Vahdat s functions will calculate the population or sample variance for a series of
numbers.
Similarly, the standard Dev, DCPI, Steve Dappy and Standard Dev that?
S functions will calculate the population standard deviations for a series of numbers.
Now, going back to your statistics courses, population means that it divides the total sum of squares
by NP, while the sample will divide by N minus one.
There's also one text aggregation function, the concatenate function will take a series of text values
and combine them together left to right.
All of these accusations have versions called ex versions.
This simply means that the name of the function has X at the end, or at least towards the end of its
name, with standard deviation and variance being outliers in that sense.
These perform the computation that the aggregation normally would by aggregating across every row from
a specified table.
We demonstrated this a few lessons ago.
To conclude aggregation functions always takes some series or array of inputs, so you have multiple
numbers and a list going into these functions and then it's going to generate a single output.
Every measure that you write in tax will involve the use of at least one aggregation function, even
our date of calculation used Max and Min functions to aggregate information down to one specific line
of output, which would be the difference between the two dates.
The standard functions for basic measures, such as some are average, will be the meat of your pivot
tables and outputs, but the X functions will be there for more specialized measures, like when we
needed our rolling computations and when you need to get really specialized measures in place.
This closes out the section on DACS for this course, but we're going to get into a few more advanced
pieces in our last lessons to close the course out covering KPIs and how to define hierarchies like
the date hierarchy, but custom to the data that you're working with.