Hello dear reader! In this article we will be talking about the most basic DAX functions of Power BI. We use these functions a lot in our daily lives. And this shows us that we will be using these while preparing reports.
Keep calm and think simple 😃 Yeap, the functions I will talk about go like this:
- Count (COUNT)
- Sum (SUM)
- Calculate the average (AVERAGE)
- Find the maximum value (MAX)
- Find the minimum value (MIN)
All these functions have a very simple structure. We usually use the functions above in measures. The results of measures give a a single scalar value. We are looking for the answers of questions below as a single scalar value.
I got the exemplary data set for application of these functions from kaggle.com. You can reach example datasets here. This data set contains information about video games like name, rank, platform, year, genre, publisher, global sales . We will be getting answers of some questions we ask to the data set with the functions above.
Let’s transfer this data set to Power BI first. For this choose Text/CSV from Get Data options on the Home tab and lead to this data set. When you click OK to get this .csv format file it will recognize the bracket between texts and display the version automatically divided into columns on the Navigation window and we will only need to click Load.
In this stage we will try to get the answer of the questions below from this data set.
1-How many games are there?
Lets’s satisfy our curiosity about this first. We will count how many games there are by using the COUNT function from the basic DAX functions. We can use any column for this action. We usually choose the columns that we know will never be empty for counting. We write the related table and column name inside the COUNT function and execute this action. You can find the related syntax below:
Game Count= COUNT(vgsales[Name])
By typing this function we find out that we are talking about 16.598 games here.
2-What is the sum of values on the Global_sales column?
When we talk about sum of values on a column another basic DAX function comes into play. We will obtain the sum of values on the GlobalSales column by using the SUM function. We have to choose a column with numeric values to use the function. The column we want the sum of contains numeric value of Decimal Number data type. We do the action by writing the related table and column name into the SUM function. You can find the related syntax below:
GlobalSales Sum= SUM(vgsales[Global_Sales])
We obtain the sum of values on the GlobalSales column as 8920.44 with this function.
3- What is the average of values on the Global_Sales column?
Let’s find the average for the same column. If we are talking about the average of values on a column, the function we will use is AVERAGE. To use AVERAGE function as well we have to choose a column that contains numeric data type just like in the SUM function. We can obtain the result we want by writing table and column name in AVERAGE function just like the two functions above. You can find the related syntax below:
GlobalSales Average= AVERAGE(vgsales[Global_Sales])
AVERAGE function gives as the answer as the value of 0.54. The average sale price of the games in this data set is 0.54.
4-What is the maximum of the values on the NA_Sales column?
This time we have another curiosity on another column. What is the maximum sales value on the NA_Sales column? The answer of this question can be provided by the MAX function only. Of course for this function too the related column has to contain numeric value. But this function gives us two syntax options: we can find the maximum value in a column, or can tell which one is bigger of two scalar values. We will request the maximum value in the whole column. The general use of MAX function is like that. You can find the related syntax below:
Maximum NA_Sales = MAX(vgsales[NA_Sales])
According to the information we obtained with this function, the maximum value on the NA_Sales column is 41.49.
5-What is the minimum of the values on the NA_Sales Column?
This is the last question we will be asking: What is the minimum sales value on the NA_Sales column? We will use the MIN function to get the answer of this question. While it has been built to bring out the minimum value, the syntax is completely same with the MAX function. The general use is to find the minimum value on a column. You can find the related syntax below:
Minimum NA_Sales = MIN(vgsales[NA_Sales])
According to the information we obtained with this function the minimum value on the NA_Sales column is 0.
With this last question we answered all the questions above by using the most basic DAX functions.
If you please, you can download the .pbix file in which all these actions are done here. Until next time, bye!
Good game well played.