Power BI

Basic DAX Functions: MIN(), MAX() and AVERAGE()

MIN(), MAX() and AVERAGE() are the basic DAX functions discussed in almost every Power BI training in Australia. These functions are intuitive and easy to use. They are often used as arguments for other functions to perform complex calculations. This blog summarizes the three functions at a basic level and their usage in complex calculations will be covered in later blogs.

Some other basic DAX functions covered in our weekly blogs so far are:

All these functions are categorized as regular functions in DAX. Regular functions simply calculate the values based on the filter selection. They are used to perform calculations for a column. In this blog, we will cover MIN(), MAX() and AVERGE() functions, which are also basic or regular functions.

The business problem under consideration for this blog is finding the minimum, maximum and average unit price per category.

MIN()

The MIN() function finds the lowest or the minimum value in any column. The syntax and basic description for MIN() is presented in the image below:Dax-functions-Min

To use MIN() function:

  1. Right click on All Measures in the Fields.
  2. Click on New measure.

Measure tools open.

  1. Type the following formula in the formula bar and press Enter.Min-code

In this Expression:

  • The minimum value of unit price in the Unit Price column from the Sales Table is calculated.
  1. Click on “$” symbol and type “2” in the text box.Dax-functions-$-2 Command

MAX():

MAX() function returns the highest or the maximum value in any column. The basic description and syntax for this function is presented below:Dax-functions-MAX

To use MAX() function:

  1. Right click on All Measures in the Fields.
  2. Click on New measure.

Measure tools open.

  1. Type the following formula in the formula bar and press Enter.MAX-code

In this Expression:

  • The maximum value of unit price in the Unit Price column from the Sales Table is calculated.
  1. Click on “$” symbol and type “2” in the text box.Dax-functions-$-2 Command

AVERAGER():

The AVERAGE() is used to find the arithmetic mean of all the numbers in the column. The basic description of the AVERAGE() function along with its syntax is presented belowDax-functions-Average

To use AVERAGE() function:

  1. Right click on All Measures in the Fields.
  2. Click on New measure.

Measure tools open.

  1. Type the following formula in the formula bar and press Enter.Average-Code

In this Expression:

  • The average value of unit price in the Unit Price column from the Sales Table is calculated.
  1. Click on “$” symbol and type “2” in the text box.Dax-functions-$-2 Command

Bringing it all Together

Now that we have calculated minimum, maximum, and average values. Let’s use the power of filter propagation in Power BI to get the desired results.

  1. Click on the matrix visual in the visualization pane.
  2. Drag and drop Category and Subcategory from the Product table to the rows.
  3. Drag and drop Minimum Unit Price (CM), Maximum Unit Price (CM) and Average Unit Price (CM) from All Measures to the Values.
  4. Click on “+” next to Bikes to expand.Dax-functions-all

Notice the maximum, minimum and average unit price per category.

If you’re new to the concept of filter propagation in Power BI then here’s a blog to help you understand the concept in detail:

Conclusion

DAX functions are used to perform calculations on the top of raw data and bridge the information gap. MIN(),MAX() and AVERGE() are three basic and commonly used DAX functions discussed in the blog above.  These functions operate quite intuitively. MIN() finds the minimum value in a column, MAX() finds the highest value in the column and AVERAGE() finds the arithmetic mean of all the values in a column. In this blog post, these functions are used to find the minimum, maximum and average unit rice per category.

Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide the following Power BI trainings:

Power BI Essentials

Power BI Advanced

Power BI DAX

 

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *