Step By Step guide to adding calculated fields in Pivot Tables

Data Analysis Expressions (DAX) is a powerful language designed for data modeling and analysis. 

It extends the capabilities of Excel and Power BI by enabling the creation of custom calculations and measures. 

The CALCULATE function is particularly noteworthy because it allows you to change the context in which your data is evaluated, leading to more dynamic and flexible analysis. 

Whats is CALCULATE?

CALCULATE modifies the filter context of an expression, allowing you to implement complex logic. 

This means you can create measures that dynamically respond to user inputs such as slicers and filters in Power BI reports or Pivot Tables in Excel. 

To begin, we need a dataset. Suppose you have the following sales data in an Excel table named 'Table4'

Next, we'll load this data into Power Pivot to create our data model.

1. Load Data: Select your table in Excel and go to the Power Pivot tab. Click on "Add to Data Model."
   Insert a screenshot of the "Add to Data Model" option in Excel.

2. Verify in Power Pivot: Open the Power Pivot window to confirm your data is loaded correctly.

Once the data is loaded in the data model you can click on the 'Manage' icon in the Power Pivot tab.

A new window will appear showing Table4 in the Data Model. 

You can close the Data Model window

Before diving into CALCULATE, let's create a simple measure to calculate total sales.

1. Open Power Pivot: Navigate to the Power Pivot window.

2. Add a New Measure: Click on the "Measures" dropdown and select "New Measure."

3. Define the measure: Create a measure for toral sales with the following DAX formula

Total Sales = Sum(Table4[TotalSales])

Formula Breakdown

• Total Sales: This is the name of the measure being created. You can use this name to reference the measure in reports, tables, and charts.

• SUM: This is a DAX function that adds up all the numbers in a column.

• Table4[TotalSales]: This specifies the column from which the SUM function will aggregate the values. "Table4" is the table name, and "TotalSales" is the column within that table.

Now, let's use the CALCULATE function to create a measure that calculates total sales for a specific category, such as "Electronics."

1. Add a New Measure: In the Power Pivot window, click on the "Measures" dropdown and select "New Measure."

2. Define the Measure Using CALCULATE: Define the measure with this DAX formula:

Total Sales Electronics CALCULATE ( [Total Sales], Table4[Category] = "Electronics")

In this measure 

• [Total Sales] is the measure previously calculated that we will be applying 

• Table4[Category] = "Electronics" is the filter that will be applied. The [Total Sales] measure will be calculated only for the items that in the column 'Category' of Table4 meet the criteria = 'Electronics'

To see the results of our CALCULATE measure, we'll use a Pivot Table.

From the 'Inser' menu in Excel click on the dropdown menu on the PIvotTable icon Then select the 'From Data Model' option

1. Insert Fields into Pivot Table: Drag and drop the necessary fields into the Pivot Table.

   • Place Region in Rows.

   • Place Total Sales Electronics in Values. Note that the measure created using CALCULATE is availbale as any other field of the original table 

2. Observe the Results: The Pivot Table now shows total sales for the "Electronics" category by region.

The CALCULATE function in DAX is a fundamental tool for anyone working with data in Power BI, Excel Power Pivot, or SSAS. 

Its ability to modify filter contexts and create dynamic, responsive measures makes it indispensable for creating sophisticated data models and reports. 

By mastering CALCULATE, you unlock the full potential of DAX and gain deeper insights from your data.