Basic DAX functions

Basic DAX functions: SUM(), DIVIDE() and COUNT()

Basic DAX functions: SUM(), DIVIDE() and COUNT()

You are currently viewing Basic DAX functions: SUM(), DIVIDE() and COUNT()

DAX stands for Data Analysis Expressions. It is the formula language used for data enrichment in Power BI, Power Pivot, and other Microsoft tools. Using DAX formulas, you can create smarter calculations and design dashboards efficiently. DAX adds on the Power of Power BI and therefore no Power BI training is complete without DAX. This blog presents a few real-life examples for some basic DAX functions as discussed in our Power BI trainings in Australia,

 Two broad categories of functions in DAX

DAX is used to bring about some meaningful information hidden inside raw data. This is done using functions in DAX. Functions in DAX perform data manipulation.

There are two broad categories of functions in DAX.

Regular Functions

Regular functions simply calculate the values based on the filter selection. They are used to perform calculations for a column.

Examples of Regular Functions in DAX are:

  • SUM()
  • COUNT()
  • AVERAGE()
  • MIN()
  • MAX()

Iterator Functions

Iterator functions are used to perform calculations for expressions. The X at the end of the functions signifies their use.

The iterator functions go through every single record of data and calculate the result of expression for that record. It then stores the results in a temporary memory. After the function has parsed the complete table, this temporary memory is released, and the aggregated results are shown.

Examples of Iterator Functions in DAX are:

  • SUMX()
  • COUNTX()
  • AVERAGEX()
  • MINX()
  • MAXX()

Let’s begin by looking at the simplest function:

SUM():

Before we create our first measure, lets quickly get affiliated with the simplest function in DAX.sum-function

To create measure:

  1. Right click on All Measures table in the Fields
  2. Click on New measure.new-measure
  1. Type the follow DAX code in the formula bar in the measure tools and press Enter.Sum-DAX-code

In this Expression:

  • The aggregated value of Total Product Cost for the entire column is subtracted from the aggregated value of Sales amount for the entire column.
  1. Click on the Profit (CM) in the All Measures table.

Measure tools menu opens,

  1. Click on the “$” and type 2 in the box.$and2-command
  2. Create another measure by following the steps 5-6.
  3. Type the following DAX code in the formula bar in the measure tools and press Enter.profit-margin-DAX-code

In this Expression:

  • The profit margin % is found by dividing the profit calculated in the previous measure by the total sales amount.
  1. Click on Profit Margin % (CM) in All Measures.

Measure tools open

  1. Click on the % sign in the formatting section.%-formatting
  2. Click on the matrix visual in the visualization pane.
  3. Drag and drop Category and Subcategory from the Products table to the rows.
  4. Drag and drop Sales Amount from Sales table to the Values.
  5. Drag and drop Profit (CM) and Profit Margin % (CM) from All Measures to the Values.

Notice the change in percentage values.

DIVIDE():Divide-function

To use DIVIDE() 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.Divide-DAX-code

In this Expression:

  • Sum of all values in the Total Product Cost column from the Sales table is calculated.
  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.markup-code

In this Expression:

  • The profit is divided by the Total cost.
  • If the division results in a mathematical error, 0 is returned.
  1. Click on % in the formatting section.% in formatting
  2. Click on the matrix visual in the visualization pane.
  3. Drag and drop Category and Subcategory from the Product table to the rows.
  4. Drag and drop Sales Amount from Sales to the Values.
  5. Drag and drop Profit (CM) and Markup (CM) from All Measures to the Values.Profit-markup-output-window

Notice the different markup values for different categories.

COUNT():count-function

To use COUNT() 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.Count-DAX-code

In this Expression:

  • The non-blank values in the Product Key column from in Product table are counted.
  1. Click on the matrix visual in the visualization pane.
  2. Drag and drop Category from the Products table to the rows.
  3. Drag and drop Total Number of Products (CM) from All Measures table to the Values.total-number-table

Notice that we have a total of 397 products with the highest number of products in the Components category.

Conclusion

DAX is a formula language which uses function calls to write meaningful calculations. There are two categories of functions in DAX: basic and iterative functions. Both of these categories have different mechanism of calculations. SUM(), DIVIDE() and COUNT() are three basic DAX functions discussed in this blog.

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:

Tags: ,

Leave a Reply