1
00:00:00,540 --> 00:00:05,940
Up to this point in the course, I've introduced you to the switching functions for conditional calculations,

2
00:00:06,210 --> 00:00:11,400
the end in all functions for logical determinations, and I've touched on some supporting functions

3
00:00:11,400 --> 00:00:14,070
like the date diff divide, ceiling and floor options.

4
00:00:14,820 --> 00:00:19,920
Amidst it all, while we've used some aggregation functions, I haven't really talked about them very

5
00:00:19,920 --> 00:00:21,530
much for this lesson.

6
00:00:21,540 --> 00:00:26,880
I'm basically going to cover what aggregation functions are, talk briefly about them and then list

7
00:00:26,880 --> 00:00:28,190
and describe each one.

8
00:00:28,440 --> 00:00:29,850
No examples here today.

9
00:00:30,450 --> 00:00:32,340
So what is an aggregation function?

10
00:00:34,230 --> 00:00:40,150
Generally speaking, aggregation functions work with a list of inputs and then generate one result,

11
00:00:40,220 --> 00:00:44,700
I think some you put in a list of numbers and then you get the total.

12
00:00:44,940 --> 00:00:49,860
DACs generally supports aggregation functions in the standard functions and then the functions that

13
00:00:49,860 --> 00:00:51,570
we discussed a few lessons back.

14
00:00:52,050 --> 00:00:55,890
For the purposes of this lesson, I'm going to focus on the standard functions.

15
00:00:56,010 --> 00:00:59,220
But note that all of these have X versions that you can use.

16
00:01:00,820 --> 00:01:06,490
Let's start with some basic occupations, basic aggregations do those things like some count and so

17
00:01:06,490 --> 00:01:06,730
on.

18
00:01:08,250 --> 00:01:12,700
The sum function takes a series of numbers, add them together and returns to total.

19
00:01:12,990 --> 00:01:14,340
We've used this a couple times.

20
00:01:14,340 --> 00:01:20,280
In this course, the count functions pretty similar, except it takes a series of numbers, counts them,

21
00:01:20,280 --> 00:01:23,910
and then returns that total in alternative form called the count.

22
00:01:23,910 --> 00:01:29,130
A function will count any item, whether it's text or numbers, and then return that total.

23
00:01:30,720 --> 00:01:36,240
Finally, there's a product function that'll take a series of numbers, multiply them together and return

24
00:01:36,240 --> 00:01:39,090
the total product of all of the numbers together.

25
00:01:41,470 --> 00:01:47,620
Also, within the basic aggregations you call the maximum functions basic, the max function looks through

26
00:01:47,620 --> 00:01:53,410
a series of numbers, finds the largest value and will return that number on the opposite end of the

27
00:01:53,410 --> 00:01:55,390
spectrum, the minimum function.

28
00:01:55,390 --> 00:02:00,190
The men will take a series of numbers, find the smallest value and return it.

29
00:02:02,570 --> 00:02:05,960
DAX has a series of statistical aggregation functions to.

30
00:02:07,310 --> 00:02:12,620
The most common is the average function, which takes a series of numbers, calculates the linear mean

31
00:02:12,630 --> 00:02:18,440
and returns that value the linear mean would be the sum of all of the numbers divided by the count of

32
00:02:18,440 --> 00:02:19,250
those numbers.

33
00:02:20,750 --> 00:02:25,910
The median function takes a series of numbers and finds the median or fiftieth percentile of them and

34
00:02:25,910 --> 00:02:27,110
returns that value.

35
00:02:27,560 --> 00:02:33,980
So if you had a list of three numbers one, two and 10, while your average would be 13 divided by three,

36
00:02:34,160 --> 00:02:38,300
your median would be two, since that's the number at the fiftieth percentile.

37
00:02:40,530 --> 00:02:46,500
The Jiemin function takes a series of numbers and calculates the geometric mean and returns that value,

38
00:02:47,040 --> 00:02:51,270
now that geometric mean will be the product of all of the numbers in your list.

39
00:02:51,630 --> 00:02:55,800
Raise the power of one divided by the count of those numbers.

40
00:02:56,490 --> 00:03:02,880
So if you had three, four and five, it would take three times four times five and raise that to the

41
00:03:02,880 --> 00:03:04,350
power of one over three.

42
00:03:07,720 --> 00:03:14,860
The Vahdat P and Vahdat s functions will calculate the population or sample variance for a series of

43
00:03:14,860 --> 00:03:15,430
numbers.

44
00:03:16,380 --> 00:03:22,740
Similarly, the standard Dev, DCPI, Steve Dappy and Standard Dev that?

45
00:03:22,740 --> 00:03:27,870
S functions will calculate the population standard deviations for a series of numbers.

46
00:03:28,620 --> 00:03:35,850
Now, going back to your statistics courses, population means that it divides the total sum of squares

47
00:03:35,850 --> 00:03:39,690
by NP, while the sample will divide by N minus one.

48
00:03:42,400 --> 00:03:48,370
There's also one text aggregation function, the concatenate function will take a series of text values

49
00:03:48,370 --> 00:03:50,350
and combine them together left to right.

50
00:03:52,430 --> 00:03:56,300
All of these accusations have versions called ex versions.

51
00:03:57,880 --> 00:04:02,920
This simply means that the name of the function has X at the end, or at least towards the end of its

52
00:04:02,920 --> 00:04:07,000
name, with standard deviation and variance being outliers in that sense.

53
00:04:07,910 --> 00:04:14,540
These perform the computation that the aggregation normally would by aggregating across every row from

54
00:04:14,540 --> 00:04:15,850
a specified table.

55
00:04:16,550 --> 00:04:18,860
We demonstrated this a few lessons ago.

56
00:04:21,240 --> 00:04:28,260
To conclude aggregation functions always takes some series or array of inputs, so you have multiple

57
00:04:28,260 --> 00:04:34,170
numbers and a list going into these functions and then it's going to generate a single output.

58
00:04:35,850 --> 00:04:42,570
Every measure that you write in tax will involve the use of at least one aggregation function, even

59
00:04:42,570 --> 00:04:49,380
our date of calculation used Max and Min functions to aggregate information down to one specific line

60
00:04:49,380 --> 00:04:52,830
of output, which would be the difference between the two dates.

61
00:04:53,840 --> 00:05:01,070
The standard functions for basic measures, such as some are average, will be the meat of your pivot

62
00:05:01,070 --> 00:05:06,890
tables and outputs, but the X functions will be there for more specialized measures, like when we

63
00:05:06,890 --> 00:05:12,110
needed our rolling computations and when you need to get really specialized measures in place.

64
00:05:12,620 --> 00:05:18,500
This closes out the section on DACS for this course, but we're going to get into a few more advanced

65
00:05:18,500 --> 00:05:25,550
pieces in our last lessons to close the course out covering KPIs and how to define hierarchies like

66
00:05:25,550 --> 00:05:29,840
the date hierarchy, but custom to the data that you're working with.