Blog

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:

• SUM(), DIVIDE() and COUNT().
• COUNTROWS() and DISTINCTCOUNT().

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:

To use MIN() function:

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

Measure tools open.

3. Type the following formula in the formula bar and press Enter.

In this Expression:

• The minimum value of unit price in the Unit Price column from the Sales Table is calculated.

4. Click on “$” symbol and type “2” in the text box.

MAX():

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

To use MAX() function:

5. Right click on All Measures in the Fields.
6. Click on New measure.

Measure tools open.

7. Type the following formula in the formula bar and press Enter.

In this Expression:

• The maximum value of unit price in the Unit Price column from the Sales Table is calculated.

8. Click on “$” symbol and type “2” in the text box.

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 below

To use AVERAGE() function:

9. Right click on All Measures in the Fields.
10. Click on New measure.

Measure tools open.

11. Type the following formula in the formula bar and press Enter.

In this Expression:

• The average value of unit price in the Unit Price column from the Sales Table is calculated.

12. Click on “$” symbol and type “2” in the text box.

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.

13. Click on the matrix visual in the visualization pane.
14. Drag and drop Category and Subcategory from the Product table to the rows.
15. Drag and drop Minimum Unit Price (CM), Maximum Unit Price (CM) and Average Unit Price (CM) from All Measures to the Values.
16. Click on “+” next to Bikes to expand.

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:

• Data Modeling: Filter Propagation in Action.

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