Statistical And Standard DAX Functions In Power BI

Introduction

In this article, we will learn about the Data Analysis Expression (DAX) functions' definition, working, and their implementation in Power BI. We will learn mostly about the Statistical, Standard, and Scientific DAX functions and the implementation on Power BI desktop.

Apart from these we will learn and implement some other important DAX functions their concept and implementation.

For this article of DAX excel sheet has been taken as data taken which are attached also for reference. To know more about how to use excel as a data source in Power BI desktop please have a look at my previous article,

Implementing Power BI – Part 1

Overview of DAX

Data Analysis Expression (DAX) is a formula language. It provides different functions for creating aggregations such as sum, count, and averages.

Basic Syntax of DAX

The syntax of DAX formulas is similar to the Excel formula but the DAX functions work on tables and columns, not on the same range as Excel that works on the range of rows or columns also.

DAX functions also differ from excel formula in some other different manner but we will learn about their implementations.

A DAX function always starts with an equal sign. These DAX functions can take a different type of data like scalar values or one or more table’s column as their argument.

Let’s learn and implement some of these important DAX functions now,

Statistical DAX

It performs the statistical operation.

Below Screen show the list of Statistical DAX function, which are Sum, Minimum, Maximum, Median, Average, Standard Deviation, Count Values and Count Distinct Values. These DAX functions replace the selected column with the resultant value.

Sum

It returns the sum of all the values in the currently selected column.

To use or implement the Sum first select the column of a table and click on Transform tab and Statistics then sum as below.

Transform – Statistics - Sum

After following the above steps the DAX function with formula as below appears with the result,

= List.Sum(#"Changed Type"[Transfer Amount])

Minimum

It returns the minimum of all the values in the currently selected column.

To implement the minimum function select a column and click on Statistics and Minimum as below.

Transform – Statistics - Minimum

Clicking on Minimum below formula appears with the result. Here 100 is the minimum value.

Maximum

It returns the minimum of all the values in the currently selected column.

Transform – Statistics – Maximum

Once you select the column and click on Statistics and then maximum followed by the above step you will get the following formula with the resultant maximum value of a column.

Here 50000 is maximum Transfer Amount.

Median

Definition: Median is the middle value of selected column range in ascending order.It returns the median of all the values in the currently selected column.

Transform – Statistics – Median

Once you select the column and click on Statistics and then Median followed by the above step you will get the following formula with the resultant median value of a column.

Here 720 is median GST Amount.

Average

Definition: Average is the sum of selected column ranges divided by the number of selected column list counts. It returns the average of all the values in the currently selected column.

Transform – Statistics – Average

Below is the Average after you click on average for selected column

Here 1363.7142857142858 is Average GST Amount.

Standard Deviation

It returns the standard deviation of all the values in the currently selected column

Transform – Statistics – Standard Deviation

You will go through the above step and you may prompt a popup; to Insert a step you have to click on Insert as below.

After clicking on Insert step you will get the result as below with the formula of standard deviation

Count Values

It returns the number of non-null values in the currently selected column.

Transform – Statistics – Count Values

Count Values doesn’t count the null values. I have replaced the value by right clicking on 7200 and again used Count Values function.

It gives 20 in place of 21 as below because it has not counted the null value.

Count Distinct Values

It returns the number of unique, non-null values in the currently selected column.

Transform – Statistics – Count Distinct Values

Standard DAX

It performs the basic math operation.

There are two sections of standard DAX functions in Transform section and in Add column section.

First, we will learn the implementation of standard DAX function in Transform section. The below image shows the standard DAX functions which are Add, Multiply, Subtract, Divide, Integer-Divide, Modulo, Percentage and Percent Of.

Let’s discuss each standard DAX function and their implementations.

Add

It adds a specified value to each number in the selected column. To add the specific value in a column select the column and click Transform – Statistics – Add

Now, you can see the result after adding the entered value as below. Here you can see that every value has been increased by 1000.

Subtract

It subtracts a specified value from each number in the selected column. To subtract the specific value in a column, select the column and click Transform – Statistics – Subtract.

Multiply

It multiplies a specified value to each number in the selected column. To multiply the specific value in a column select the column and click Transform – Statistics – Multiply.

Divide

It divides each number into the selected column by a specified value. To divide the specific value in a column select the column and click Transform – Statistics – Divide.

Integer-Divide

Integer-divides each number in the selected column by a specified value.

Transform – Statistics – Integer-Divide.

Modulo

It calculates the remainder of dividing each number in the selected column by a specified value.

Transform – Statistics – Modulo.

After modulo the result will be as below

Percentage

Calculate a specified percentage of the values in the selected column.

Transform – Statistics – Percentage.

After clicking on OK, the result will be as below. The result of it is10 percent of each value.

Percentage Of

It calculates the values in the selected column as a percentage of a specified value.

Transform – Statistics – Percentage Of

The output will be as below as it checks the given value is what the percentage of provided value.

Summary

In this article, we have learned the different types of Statistical and Standard DAX functions which are used in Power BI. We have also learned what is the basic definition of DAX and their syntax overview.

Hope you will learn from this article.