Excel 2013 PowerPivot Basics #10: CALCULATE function to Change Filter Context (14 Examples) скачать в хорошем качестве

Excel 2013 PowerPivot Basics #10: CALCULATE function to Change Filter Context (14 Examples)

Download file: http://people.highline.edu/mgirvin/ex... Scroll to down to PowerPivot Section. Topics in this video: 1. (00:34 min mark) Essence of PivotTable 2. (01:08 min mark) Overview of Filter Context and CALCULATE 3. (02:30 min mark) Look at how Filter Context works on a Calculated Field 4. (03:53 min mark) COUNTROWS function to see “Filtered Table” 5. Example 1: (05:37 min mark) CALCULATE and SAMPERIODLASTYEAR. 6. (08:30 min mark) % Change Formula For Years. 7. (09:27 min mark) ISFILTERED function 8. (10:53 min mark) % Change Formula For Years Correct formula using two IF functions and the BLANK function. Finished PivotTable Report for Change by Year for each product. 9. (14:07 min mark) Example of reusing Calculated Field to make new report. Two additional Reports. 10. Example 2: (15:25 min mark) CALCULATE and the ALL function to create DAX formula for % of Grand Total. DIVIDE function also. 11. Example 3: (19:30 min mark) CALCULATE and Boolean Condition with one criteria on the year column. 12. (22:45 min mark) Formula for comparing change in Revenue compared to base year 2014. ISFILTERED and BLANK functions also. 13. Example 4: (24:55 min mark) CALCULATE and Boolean Condition with two AND criteria: year and product columns. 14. Example 5 & 6: (27:08 min mark) 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. 15. (30:33 min mark) Overview of Adding Sales by Category and Frequency Tables. 16. (31:53 min mark) MIN and MAX functions. Example of these functions in PivotTable. 17. Examples 7 & 8: (33:19 min mark) Adding Sales by Category and Frequency Tables using CALCULATE, MIN, MAX and FILTER Functions in a single Calculated Field. 18. (35:11 min mark) Example of Ampersand for AND Criteria or AND conditions. 19. (36:45 min mark) Test to see if newly added data will fit into our Sales Categories. 20. (37:46 min mark) Update Excel File with Sales Category Table and Refresh Data. 21. Examples 9 & 10: (41:05 min mark) 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. 22. Examples 11 & 12: (46:41 min mark) CALCULATE to transition between Filter Context to Row Context. Learn that all Calculated Fields have an invisible CALCULATE function wrapped on the outside. 23. Examples 13 & 14: (48:36 min mark) Compare two Boolean Criteria in CALCULATE function with two conditions in the FILTER Function.