Introducing the DAX Language for Excel Pivot Tables  

Before we start using DAX, we need to ensure that Power Pivot is enabled in Excel. Power Pivot is an Excel add-in that provides advanced data modeling features, which DAX utilizes.

1. Open Excel.

2. Go to File > Options.

3. Select Add-Ins from the left-hand menu.

4. In the Manage box, select COM Add-ins and click Go.

5. Check the box next to Microsoft Office Power Pivot and click OK.

We start by a simple table in Excel

The next step is to load your data in the Power PIvot model 

1. Select one cell in the Excel Table 

2. Go to the PowerPIvot Tab

3. Click on Add to Data Model 

4. Save and close the window that will appear

Now that your data is loaded into Power Pivot, we can create a Pivot Table.

1. Go to the Insert tab in the Excel ribbon.

2. Click on the drop down menu of the  PivotTable button and click on From DataModel

3. Select the NewWorkSheet option and click OK

4. The table previously saved in the DataModel shows in the window of the pivot table fields. In our case the table was called Table4 1 and it is available in the Pivot Table options

We place the Region in the Row section and the Product in the column section 

DAX measures are calculations that you can use in your Pivot Table. Let's create a simple measure.

1. Go back to the Power Pivot tab in the Excel ribbon.

2. Click on Measures and then select New Measure.

3. In the Measure dialog box, provide a name for your measure and enter the DAX formula. For example, to calculate total sales, you could use:

SumOfSales = SUM(Table4 1[Sales])

SUM: is the function that you want to use

Table4 1: is the name of the table to which you wanto to apply the measure

Sales: is the column to which you want to apply the measure. 

Note that the name of the column is between squared brackets 

The measure SumOfSales sums all the values in the column Amount in table Table4 1

If you want to know what are the formulas available in the DAX language, click on the fx button available in the Measure window. 

An extensive list of functions will display 

Once the DAX Measure is created it will display together with all the other fields available to create the Pivot Table

The Measure can now be used and inserted in the Values section of the Pivot Table

The SumOfSales measure as all DX measures, operates within the filter context represented by the Rows and Columns. Therefore when a DAX measure is placed within a Pivot Table it reflects the result of only the data that matches the row and the column filters. 

The SumOfSales measure has been created to sum all the values in the column Sales of the Excel Table, once it is placed in the Values section, each cell will reflect the sum of data that matches the corresponding Region and Product combination

We have explained the basics of using DAX in Excel Pivot Tables. 

By following these steps, you can create powerful, custom calculations that enhance your data analysis capabilities. 

Keep experimenting with different DAX functions to unlock even more insights from your data.