# Excel 2013 PowerPivot Basics 10

CALCULATE function to Change Filter Context (14 Examples)

### DESCRIPTION

Topics in this video:

**(00:34)** Essence of PivotTable

**(01:08)** Overview of Filter Context and CALCULATE

**(02:30)** Look at how Filter Context works on a Calculated Field

**(03:53)** COUNTROWS function to see “Filtered Table”

**Example 1**

**(05:37)** CALCULATE and SAMPERIODLASTYEAR.

**(08:30)** % Change Formula For Years.

**(09:27)** ISFILTERED function

**(10:53)** % Change Formula For Years Correct formula using two IF functions and the BLANK function. Finished PivotTable Report for Change by Year for each product.

**(14:07)** Example of reusing Calculated Field to make new report. Two additional Reports.

**Example 2**

**(15:25)** CALCULATE and the ALL function to create DAX formula for % of Grand Total. DIVIDE function also.

**Example 3**

**(19:30)** CALCULATE and Boolean Condition with one criteria on the year column.

**(22:45)** Formula for comparing change in Revenue compared to base year 2014. ISFILTERED and BLANK functions also.

**Example 4**

**(24:55)** CALCULATE and Boolean Condition with two AND criteria: year and product columns.

**Example 5 & 6**

**(27:08)** CALCULATE and Boolean Condition with two OR criteria. See the syntax for OR Criteria and the OR function. Report to show Total Revenue for Beginner Boomerang sales using OR Criteria.

**(30:33)** Overview of Adding Sales by Category and Frequency Tables.

**(31:53)** MIN and MAX functions. Example of these functions in PivotTable.

**Examples 7 & 8**

**(33:19)** Adding Sales by Category and Frequency Tables using CALCULATE, MIN, MAX and FILTER Functions in a single Calculated Field.

**(35:11)** Example of Ampersand for AND Criteria or AND conditions.

**(36:45)** Test to see if newly added data will fit into our Sales Categories.

**(37:46)** Update Excel File with Sales Category Table and Refresh Data.

**Examples 9 & 10**

**(41:05)** Adding Sales by Category and Frequency Tables using CALCULATE, FILTER, Row Context in a Calculated Column. The formula simulates what a “Approximate Match VLOOKUP” formula would do in Excel. Then build a relationship to build reports.

**Examples 11 & 12**

**(46:41)** CALCULATE to transition between Filter Context to Row Context. Learn that all Calculated Fields have an invisible CALCULATE function wrapped on the outside.

**Examples 13 & 14**

**(48:36)** Compare two Boolean Criteria in CALCULATE function with two conditions in the FILTER Function.