|Power BI training in Sydney

|Power BI training in Melbourne

Power BI DAX Functions: MIN(), MAX(), AVERAGE()

You are currently viewing Power BI DAX Functions: MIN(), MAX(), AVERAGE()

DAX Functions are an integral part of every Power BI training because Power BI is incomplete without the knowledge of DAX and DAX is incomplete without the understanding of DAX Functions.

Last month, we started a series a blogs to strengthen your learning related to DAX. In the first month, we developed sound foundation to understand DAX and now are working on ‘DAX Functions’ in our ‘DAX Weekends’ series. In this blog post, the MIN(), MAX() and AVERAGE() functions have been discussed. The data set used for this blog post is Adventure Works DW 2020. It is a sample data model developed by Microsoft to enhance the DAX learning of their users. The Adventure Work dataset represents the data of fictitious bicycle manufacturer that sells bicycles and accessories to global markets.

Adventure Works Model Structure

The model contains seven tables:

Source: https://docs.microsoft.com/en-us/power-bi/guidance/dax-sample-model

This is what your data model looks like once you have loaded the Adventure Works data set into Power BI desktop.

The model does not contain any DAX calculations. So we begin by following the best practice.

BEST PRACTICE TIP: Store all measure in a separate measure table.

1.Click on Home Tab in the Ribbon Menu.

2.Click on Enter Data in the Data Section.

3.Name Column1 to Measures.

4.Name the Table to All Measures.

  1. Click on Load.

Notice that a single column table which does not contain any data is created. 

MIN()

To create a measure that calculates the minimum value:

  1. Right on All Measures Table in the Fields Pan.
  2. Click on New Measure.
  3. Create a Measure with the name ‘Minimum Standard Cost’ that finds the minimum value of Standard Cost Column in the Product Table.

To see what is the value for the minimum standard cost.

4.Select a card visual from the visualization pane.

5.Drag and drop ‘Minimum Standard Cost’ from Measures Table to the Fields.

Lets verify this value from the Data without using DAX.

6.Click on Data in the left Ribbon.

7.Select Product Table from the Fields Pan.

8.Click on Standard Cost Column and Sort Ascending.

Notice that the value in the first row is same as the value in the card visual.

MAX()

To create a measure that calculates the Maximum Value:

  1. Right on All Measures Table in the Fields Pan.
  2. Click on New Measure.
  3. Create a Measure with the name ‘Maximum Standard Cost’ that finds the maximum value of Standard Cost Column in the Product Table.

To see what is the value for the minimum standard cost.

4.Select a card visual from the visualization pane.

5.Drag and drop ‘Maximum Standard Cost’ from Measures Table to the Fields.

Lets verify this value from the Data without using DAX.

6.Click on Data in the left Ribbon.

7.Select Product Table from the Fields Pan.

8.Click on Standard Cost Column and Sort Descending.

Notice that the value in the first row is same as the value in the card visual.

AVERAGE()

To create a measure that calculates the Average Value:

  1. Right on All Measures Table in the Fields Pan.
  2. Click on New Measure.
  3. Create a Measure with the name ‘Average Standard Cost’ that finds the average value of Standard Cost Column in the Product Table.

To see what is the value for the minimum standard cost.

4.Select a card visual from the visualization pane.

5.Drag and drop ‘Average Standard Cost’ from Measures Table to the Fields.

Applying Filter Context:

Up till now, we have used DAX expressions in an over simplified manner.  DAX expressions are evaluated using certain rules. 

Here’s a complete guide to how evaluation is performed in DAX.

https://powerbitraining.com.au/category/dax/

Let’s walk through two examples and see how does the incoming filter context effect the value of measures. 

Incoming Filter context due to the Visual:

To see the effect of filter context due to a visual.

  1. Select Table visual from the Visualization Pane.
  2. Drag and drop ‘Minimum Standard Cost’ from the All Measures table to the Values.
  3. Drag and drop ‘Maximum Standard Cost’ from the All Measures table to the Values.
  4. Drag and drop ‘Average Standard Cost’ from the All Measures table to the Values.

Notice that each measure is calculated with respect to a particular category only and the value is displayed. Notice the Total Field Shows the Minimum, Maximum and Average Value from the visual results.

Incoming Filter context due to the Slicer:

Lets dig in further and add slicers to our report.

5.Select Slicer from The Visualization Pane.

6.Drag and drop Categories column from the Product Table to the Fields.

7.Drag and drop Subcategories column from the Product Table to the Fields.

 

Noyice that the Slicer autometically detects the hierarchy in your data.

8.Select Bikes from the Category.

9.Select Touring Bikes from the Subcategory.

Notice the change in the values displayed on the table visual. This means that the incoming filter context from the table visual and the slicers has been evaluated before the final result is displayed.

Verification of Minimum Value:

Now let’s verify the values from the Data.

  1. Click on Data in the left Ribbon.
  2. Select Product Table from the Fields Pan.
  3. Click on the drop down menu next to Category and Select Bikes.

4.Click on the drop down menu next to Subcategory and Select Touring Bikes.

5.Click on the Standard Cost Column and select ‘Sort Ascending’.

Notice that this is the same value displayed using DAX functions.

Verification of Maximum Value:

6.Click on the Standard Cost Column and select ‘Sort Descending’.

Notice that this is the same value displayed using DAX functions.

Verification of Average Value:

To verify the results for average calculation using DAX, we find the average value manually using mathematical formula.

Total number of Rows = 22

Sum of Standard Costs =19490.12

Average value = Sum of Standard Costs / Total number of Rows =885.914

Conclusion:

DAX is a formula language and is a collection of functions and operators used to perform a calculation. In this blog post, the MIN, MAX and AVERAGE Functions have been discussed and verified. The effect of incoming filter context is also explored using visual and slicers.

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 our services in the following regions

Leave a Reply