Excel 2013 PowerPivot Basics 10

CALCULATE function to Change Filter Context (14 Examples)


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
(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.