In this article, we will learn about the syntax of **DAX **formulas for Power Pivot. We will also learn where to use **DAX **formulas and how to use **DAX **formulas for Power Pivot.

We can use the **DAX **formulas for Power Pivot in various business situations. You can apply formulas for sales analysis, inventory management, budgeting, and forecasting, etc.

When we need to find any row-wise data from the given columns (e.g. sales from the multiplication of unit price and quantity), more importantly, in the calculation area, we can determine a specific value based on single or multiple criteria using the **Measures **command.

**Download Practice Workbook**

You can download the practice file here.

**Table of Contents**Expand

## Syntax of DAX Formulas

**DAX **(Data Analysis Expressions) is a formula language for Power Pivot and Power BI.

The **DAX **formula contains multiple components. These are:

**Measure Name:**The name of the calculation. You can rename it according to your preferences.**DAX Function:**There are more than 250 DAX functions. You have to input the appropriate functions. Remember, there is a difference between Excel functions and**DAX**functions.**Table Name:**Sometimes, it will be in a single quote.**Column Name:**It will be in square brackets.

You can better understand the syntax from the following visual representation.

## Where to Write DAX Formulas in Power Pivot

We can use **DAX** formulas in two different places in Power Pivot. We will discuss these in the following part of the article.

### 1. Calculated Column

A calculated column in Power Pivot is a column you can add to a table using Data Analysis Expressions (**DAX**) formulas. You can perform calculations based on the values in other columns of the same table or other related tables by using **DAX **formulas. Following that, the calculated values for each row in the table are placed in the calculated columns.

- First, click on the
**Add Column**named column’s header. - Then, in the formula bar, enter your formula. In the highlighted red box area.

- After that, press the
**Enter** - Finally, the formula result will appear in the calculated column.

### 2. Measures Command in Power Pivot

A measure in Excel’s Power Pivot is a **DAX **formula that computes an aggregate, calculation, or result based on a set of values.

- To start, select
**New Measure**under**Measures**on the**Power Pivot**tab.

- After that, the
**Measure**dialog box will appear.

In the formula box, you can write the **DAX **formula. Some other things that you can specify (optional) **Measure Name**, description, etc. Another important thing is to choose the **Table Name **to apply the **DAX **formula.

- After writing the
**DAX**formula, you have to press**OK**.

## Prepare Data to Apply DAX Formulas for Power Pivot

We will be using a workbook in Excel with three separate worksheets. These sheets contain data about **“Customer Information”**,** “Invoice Log”**, and **“Invoice Details”**. You must first prepare your dataset before using **DAX **in Power Pivot. Follow these steps:

- First, we will convert the dataset from each sheet into a table.
- Now, you have to give each table a meaningful name so you can find it when writing
**DAX**Click on**Table Design**and then edit the**Table Name**.

- Then, launch the Power Pivot’s
**Data Model**and navigate to the sheet you want to include. - Following that, select
**Add to Data Model**from the Power Pivot tab. - Next, follow the same steps to add the remaining tables to the data model.

- After that, go to the
**Power Pivot**tab and choose the**Manage**option.

- Consequently, Excel’s Power Pivot interface will become visible.

- Now, we will create a relationship between the tables. We can do that in the
**Diagram View**.

- Following that, we will choose the name of the table’s field that you will use to establish a relationship with another table.
- Then, drag the field to the corresponding field in the second table.

- After that, we can create the relationship between the tables
**“CustomerInfo”**and**“InvoiceLog”**based on the common field.*CustomerID*

- Next, create all the relationships by dragging and joining the common headers.

- Subsequently, click on the
**Data View**if you have created the relationships. - As a result, the
**Data View**of the sheets will appear.

- You can now use
**DAX**functions and formulas to get the value of the**calculated field**. - Here, we will insert a formula in the calculated column of the
table.*InvoiceDetails*

`=Total Revenue:=InvoiceDetails[Quantity]*InvoiceDetails[Unit Cost]`

- This
**DAX**formula will show all the order revenues in a new column.

## How to Use DAX Formulas for Power Pivot: 5 Practical Examples

### 1. AVERAGE Function

**The AVERAGE function** calculates the average of a numeric column or expression in a table. It determines the total number of values in the chosen column and divides that total by the number of non-blank values.

**Syntax:**

`AVERAGE(<column>)`

We can use **the AVERAGE function **in **DAX **to calculate average values from the dataset.

- First, select a cell outside the table.
- Next, insert the following formula in the table.

`Average Sales Per Order:=AVERAGE(InvoiceDetails[Total Revenue])`

- As a result, we will see average sales per order.

- If you notice, you will see that the result value contains infinite values after decimal.
- So, keep the cursor on the cell and right-click on the mouse.
- After that, it will show the
**Format**option; click on that.

- Next, a dialog box will appear. Set the category to
**Number**and set your desired decimal places.

- Finally, it will change the decimal values of the result.

### 2. SUMX Function

**The SUMX function** in **DAX **is used to loop through a table and accumulate totals. It processes each row by evaluating an expression and adding up the values. It is useful when you need to run a calculation on each row of a table and then add up the results to get a grand total.

**Syntax:**

`SUMX(table, expression)`

We need to multiply the **Quantity **and **Unit Price **of each order, then add the results to determine the **“Total Sales Revenue”**. **The SUMX function** in **DAX **is the only function that is suitable in this situation.

- First, select a cell and insert the following formula in the formula bar.

`Total Sales Revenue:=SUMX(InvoiceDetails,[Quantity]*[Unit Price])`

- Then, the formula will return the
*Total Sales Revenue**,*counting all invoices.

### 3. CALCULATE Function

**The CALCULATE function** in **DAX **is one of the most useful and robust functions available. This is useful for changing the context in which a **DAX **expression is evaluated. This is particularly helpful for applying filters, altering the row context, and running conditional calculations.

**Syntax:**

`CALCULATE(<expression>, <filter1>, <filter2>, ...)`

In this dataset, we will calculate the total number of pending orders. Here, we will combine **the CALCULATE function **with the **COUNTROWS **function.

- First, select a cell and insert the following formula in the formula bar.

`Total Pending Orders:=CALCULATE( COUNTROWS(InvoiceLog), InvoiceLog[Payment Status] = "Pending" )`

**Formula Breakdown**

**COUNTROWS(InvoiceLog):**This formula counts the number of rows in the**“InvoiceLog”**It gives you the total number of orders, regardless of payment status.**InvoiceLog[Payment Status] = “Pending”:**This is a filter condition. It restricts the calculation to include rows where the**“Payment Status”**column has the value “Pending”.**CALCULATE( COUNTROWS(InvoiceLog), InvoiceLog[Payment Status] = “Pending” ):**This function allows you to modify the filter context of a calculation. It takes an expression as its first argument and optional filter conditions as subsequent arguments.

### 4. FILTER Function

**The FILTER function** in **DAX **filters data taken from a table based on a predetermined set of conditions that the user specifies. It will return a table that contains only those rows in the table that meet the criteria that you provide.

**Syntax:**

`FILTER(Table, Condition)`

I only want the sales for quantities under **“5”** to be calculated in my dataset. In this instance, I will combine **the SUMX function** and **the FILTER function**.

- In the beginning, select a cell where you want to see the result.
- Then, insert the following formula in the formula bar.

`Total Sales for Quantity<5:=SUMX(FILTER(InvoiceDetails,[Quantity]<5),InvoiceDetails[Quantity]*InvoiceDetails[Unit Price])`

**Formula Breakdown**

**FILTER(InvoiceDetails, [Quantity] < 5):**This function filters the**InvoiceDetails**table to include only the rows where the quantity is less than 5.**SUMX(FILTER(InvoiceDetails,[Quantity]<5), InvoiceDetails[Quantity]*InvoiceDetails[Unit Price]):**This part calculates the sum of the product of quantity and unit price for each row in the filtered table from step 1. SUMX loops through the filtered table, multiplies each row’s quantity by its unit price, and then adds up the totals.

### 5. IF Function

**The IF function** in **DAX **checks if a condition is true or false and returns a different value depending on the result.

**Syntax:**

`IF(LogicalTest, ValueIfTrue, ValueIfFalse)`

We will use **the IF function **to determine which orders are retail and which are bulk orders.

- First, we select a new column and insert the following formula.

`=IF(InvoiceDetails[Quantity] >= 10, "Bulk", "Retail")`

- As a result, it will return the result in the rows of that particular column.

## Things to Remember

- It is very important to set up the right relationship between the tables. Unexpected outcomes can result from missing or incorrect relationships.
- In your
**DAX**formulas, always double-check the names of the columns and tables. Errors will occur if the name of the data model and the formula inputs are different.

## Frequently Asked Questions

**1. How to use the DAX function in Power Query?**

Power Query does not directly use **DAX **functions. Power Query shapes and transforms data using the** ” M ” **programming language. While Power Query handles data preparation and transformation before the data is loaded into the model, **DAX **is typically used in measures and calculated columns within the data model.

**2. What is the use of the Related () DAX function?**

The `**RELATED()**` **DAX **function retrieves values from a related table in a one-to-many or many-to-one relationship within a data model.

**3. Is there any difference between Calculated Columns and Measures?**

**Measures **and **Calculated Columns** differ in a number of specific ways. **Calculated columns **are performed while data is being loaded or refreshed. Once the calculations are completed, a new column is added to the table with the results. However, Measures are specifically made for aggregating data and performing calculations that involve summarizing it, such as sums, averages, counts, ratios, etc.

## Conclusion

In this article, we discussed the syntax of **DAX** formulas in Excel. We also discussed where and how to use **DAX** formulas for Power Pivot in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

**<< Go Back to DAX in Power Pivot | Power Pivot Excel | Learn Excel**