Download Start Files: https://ift.tt/2T3OFDi
Download Finished Files: https://ift.tt/2EdyTM2
Pdf notes: https://ift.tt/2T0eiFf

This video teaches all the tricks of PivotTables from Slicers to Show Values As to Dashboards. Learn how to create Cross Tab Repots, Dashboards, Frequency Distributions from Text Data and a CPA Pass Rate Report.
This class : Data Analysis & Business Intelligence Made Easy with Excel Power Tools – Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Desktop and more.

Topics:
1. (00:12) Introduction
2. (01:35) What is a PivotTable? What is a Cross Tab Report.
3. (03:20) Build a Cross Tab Report with a PivotTable
4. (11:15) PivotTable Cached Data
5. (12:10) Building a Standard PivotTable as Part of Dashboard
6. (12:54) Standard PivotTable vs. Data PivotTable
7. (14:18) What is a Dashboard?
8. (17:20) Use Group By Feature to group Monthly and Yearly Amounts
9. (18:55) Summarize Values By to Change Aggregate Function
10. (19:35) Use Slicers to Filter Entire PivotTable
11. (23:21) Cell Phone Data Examples from Video to Build Frequency Distribution:
12. (24:18) Show Values As to Change to Specific Calculations
13. (25:20) CPA Data Examples from Video to Build CPA Pass Rate Report
14. (27:47) Practice Problems, Homework
15. (27:57) Summary

View on YouTube

Download Start Files: https://ift.tt/2BNhIjZ
Download Finished Files: https://ift.tt/2U1H2K4
Pdf notes: https://ift.tt/2BJxrRa

This video #2 in our class teaches about Data, Data Types, Proper Data Sets, Excel Tables, Soring, Filtering and Logical Tests.
This class : Data Analysis & Business Intelligence Made Easy with Excel Power Tools – Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Desktop and more.
Topics:
1. (00:12) Introduction
2. (01:14) What is Excel? What about the Data in the Cells?
3. (08:30) What is Data, Raw Data?
4. (10:01) Understanding the Difference Between Data & Information.
5. (10:45) Define Proper Data Set.
6. (in pdf notes) Why Proper Data Sets are Mandatory.
7. (14:25) Tables are not Charts
8. (14:50) Use Excel Tables For Dynamic Data. Learn about Excel Table Feature.
9. (20:00) Data Types in Proper Data Sets in Various Tools
10. (24:00) How to Sort Data.
11. (26:07) Filtering & Extracting Data.
12. (28:47) Understand & Use AND Logical Tests and OR Logical Tests
13. (34:35) Summary

View on YouTube

Download Excel Start Files: http://bit.ly/2EfpEfI
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to lookup numbers on either side of a hurdle using MAXIFS and MINIFS Functions which are available in Office 365 or Excel 2019. This trick comes from brianxyz at YouTube!
Original question:
I have 3, 6, 7, 13, 15, 18, 19 in a column and I want the first number greater and less than 11. (I want 7 in a cell and 13 in a different cell).

View on YouTube

Download Excel Start Files: http://bit.ly/2N3rHGj
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to Add Sales Between Upper & Lower Date-Time with SUMIFS. Learn about Date and Time Values in Excel.

View on YouTube

Download Excel Start Files: http://bit.ly/2N3rHGj
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to extract sales records based by day with a formula. Learn about the New Office 365 Dynamic Array Functions: FILTER, SORT and UNIQUE. Compare and contrast this new method to the way we used to accomplish it in the old days with complicated Array Formulas.

View on YouTube

All 20 files used in video are available in this zipped folder: http://bit.ly/2BfCEjj
Download file individually at class web site: http://bit.ly/2TrKPQT
pfd notes for Video #19: http://bit.ly/2BeWn2M
Comprehensive video about the CALCULATE DAX Function and how Filter Context works to calculate DAX Formulas in Power BI and in Excel Power Pivot.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction
2. (01:27) Filter Context, First Look
3. (04:40) CALCULATE to change Filter Context with a Boolean Filter
4. (05:10) CALCULATE and CALCULATETABLE DAX Functions
5. (06:28) What is a Boolean Filter?
6. (08:00) First look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context
7. (09:45) CALCULATETABLE to change Filter Context with a Boolean Filter
8. (11:25) CALCULATE to perform Context Transition
9. (13:17) All Measures have a Hidden CALCULATE Function
10. (14:20) Boolean Filter is always converted to a FILTER & ALL DAX Function construction
11. (17:22) DAX Studio to look at FILTER and ALL Equivalent for a Boolean Filter
12. (20:00) VALUES rather than ALL in first argument of FILTER
13. (22:05) VALUES Function to bring a Variable into a Formula and use it as a condition for a Boolean Filter
14. (24:22) AND Logical Test – 4 Examples
15. (26:18) Can NOT use two different columns in a Boolean Filters
16. (28:30) OR Logical Test Boolean Formulas
17. (30:33) Can NOT Directly Compare Two Columns as a Boolean Filter. Keystone Pricing Examples.
18. (33:20) DAX Studio to see how FILTER and ALL delivers a table where we directly compare two columns for a DAX Measure
19. (34:00) Can NOT use MIN or MAX or other aggregate functions as a condition for a Boolean Filter
20. (34:00) Frequency Distribution DAX Formula. Learn about the COUNTROWS Function
21. (43:07) ALL DAX Function & Grand Totals. Learn about the Remove Operator in ALL DAX Function when you use it in the Filter argument of CALCULATE
22. (47:42) Look at Data Model for Power BI File for looking more closely at the Overwrite Operator
23. (49:00) Overwrite Operation. Second look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context into the Final Filter Context
24. (50:50) Reminder about ALL before we learn about ALLSELECTED. What is the problem that ALLSELECTED can solve?
25. (52:35) ALLSELECTED to create Grand Totals that respect the filtering in the PivotTable
26. (53:33) Context Transition is how ALLSELECTED gets to the Grand Total Cell Filter Context
27. (53:45) Hidden Context Transition for Measures in an Excel PivotTable or Power BI Visual
28. (54:45) ALLSELECTED and Context Transition, the Full Story
29. (56:17) Visual Example of when ALLSELECTED will not get back to the Filter Context of the Grand Total Cell in the Excel PivotTable or Power BI Visual
30. (57:18) Use ALLSELECTED DAX Function in CALCULATE Measures
31. (59:05) ALLSELECTED Functions works on Cross Tab Reports too !
32. (59:44) Example of Measure in Power BI Visual that uses ALLSELECTED and will not get back to the Filter Context of the Grand Total Cell Power BI Visual
33. (01:01:20) Look at new Data Model for next example
34. (01:02:20)First example of the KEEPFILTERS DAX Function to force an AND Logical Test rather than an Overwrite Operation
35. (01:06:55) What is a Complex Filter? What is a Complex Filter Reduction Error?
36. (01:08:56) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column in AVERAGEX.
37. (01:14:47) Why KEEPFILTERS is used in the New Quick Measure in Power BI
38. (01:16:33) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column and Month Column in CROSSJOIN in AVERAGEX
39. (01:18:30) Data Model solution to solve Complex Filter Reduction Error
40. (01:20:05) Determine if a filter is a complex filter with CROSSJOIN
41. (01:21:17) Expanded Table Concept & Relationships.
42. (01:26:50) Table Filters & Expanded Table as filters in the Filter argument of CALCULATE
43. (01:27:15) Expanded Table Filter with ALL Function. 2 Examples
44. (01:29:15) Table Filter to send a filter backwards across a Many-To-One Relationship. Great Visual for understanding this filter
45. (01:33:10) ALLEXCEPT DAX Function with an Expanded Column
46. (01:37:00) Examples of Time Intelligence Functions
47. (01:37:20) Measure for Revenue from Last Year. SAMPERIODLASTYEAR
48. (01:38:40) Measure for Revenue for Last Month. DATEADD and ISFILTERED DAX Functions
49. (01:41:32) Compare Excel and DAX Formulas
50. (01:43:10) Measure for % Change using IF, BLANK, DIVIDE and HASONEVALUE DAX Functions
51. (01:46:15) DAX Formula Evaluation Context Summary

View on YouTube