Excel Magic Trick 1366

DESCRIPTION

Full Lesson on Power BI Desktop to build Product Analysis for Gross Profit with Average, Standard Deviation, Coefficient of Variation and Histogram Calculations and Visualizations:(00:04) Files to download(00:12) Introduction(04:42) Import Related Tables from Access(05:42) Edit automatic Relationships Bi-directional Filtering to Single-directional Filtering(07:22) Import Text Files From Folder(08:36) Filter out file extensions that are NOT Text .txt(09:38) Use “Combine Binary” Icon to combine Text Files into one table(10:40) Look at “Combine Binary”: Query Creation Steps, including M Code and Power Query Function that is automatically created(12:23) Change Data Types in fSales (Fact Sales) Table(13:23) edit Relationship between fSales Product Table(14:14) Create Calendar Table in Excel(18:33) Create Frequency Distribution Category Table in Excel using Text Formula(21:39) Import tables from Excel File(22:52) Manually Create Relationships Between Tables(23:40) Create DAX Calculated Column for Net Revenue using the RELATED function (works like VLOOKUP Exact Match in Excel) & ROUND function. Net Revenue values are stored in the “In RAM Memory” Data Model
Discuss Convention for using Columns in formulas: ALWAYS USE TABLE NAME AND COLUMN/FIELD NAME IN SQUARE BRACKETS(26:24) Look at How REALTED works across relationships(27:07) Discussion of Row Context(29:25) Create Measure for Total Revenue. This Measure is a Measure that is based on values in a Calculated Column(31:15) Add Number Format to Measure so that every time the Measure is used the Number Format will appear(31:53) Learn about Measures that are not dependent on Calculated Columns. See how to create Measure that does not use a Calculated Column as a source for values. UseSUMX function(34:59) and (36:40) Compare creating: 1) Measures based on Calculated Columns and or Measures not based on Calculated(35:39) and (42:40) Discussion of Filter Context and how it helps DAX formulas calculate Quickly on Big Data. Filter Context: When a Conditions or Criteria are selected from the Lookup Tables (Dimension Tables) they flow across the Relationships from the One-Side to the Many-Side to Filter the Fact Table down to a smaller size so that the formulas have to work over a smaller data set(36:52) and (37:52) Discussion of how values created in Calculated Colum are stored in the Data Model Columnar Database and this uses RAM Memory(38:54) When you must use a Calculated Column: When you need to extend the data set and add a column that has Conditions or Criteria that you want to use to Filter the Data Set(40:06) Create Calculated Column For COGS using ROUND and RELATED Functions(41:50) Create Calculated Column for Gross Profit(43:35) Create Calculated Column on fSales Table that will create the Sales Categories “Retail” or “Wholesale” using IF & OR functions. Because it creates Criteria that will use as Filters for our Measures, This DAX formula can only be created using a Calculated Column, not a Measure(46:00) Measure for Total COGS(46:36) Measure for Total Gross Profit(47:20) Measure for Gross Profit Percentage. This is a Ratio of two numbers. This is an example of a Measure that can ONLY be created as a Measure. It cannot be created as a Measure based on a Calculated Column(48:35) Discuss Convention for using Measures in other Measures: USE SQUARE BRACKETS ONLY around the Measure name(49:52) Measure for Average (Mean) Gross Profit(50:20) Measure for Standard Deviation of the Gross Profit(51:09) Measure for Coefficient of Variation of the Gross Profit(52:43) Hide Unnecessary Columns from Report View(53:01) Sort Month Name Column by Month Number(54:19) Sort Category Column By Lower Limit(55:25) Add Data Category Image URL for Image File Paths(57:10) Create DAX Column to simulate Approximate Match Lookup using the FLOOR function(59:54) Manually Create Relationship For Category Table(01:00:18) Update Excel Table and Test to see if Power BI Report Updates when we Refresh(01:01:57) Create Product Analysis Visualization with the first visualization: Create Table with Product Pictures and Metrics. This is Page one of our Power BI Report.(01:03:13) Create Bar Chart For Mean and Standard Deviation of Gross Profit(01:03:39) Create Slicers to Filter Visualizations(01:04:11) Create Frequency Distribution Table & Measure to Count Transactions(01:05:35) Format Table, Chart and Slicers(01:07:45) Create second Page in Power BI Report with Product Revenue and COGS by Year & Month(01:09:05) Publish Power BI Report online(01:10:37) Generate Embed code for e-mailing Report and for embedding in web sites(01:11:38) Summary

DESCRIPTION Learn how to look up an item in a list of items and list all the relative positions for that item in a single cell. See the functions TEXTJOIN, IF, ISNA, MATCH. See extensive [Read More >>>]

Extract Multiple Records Based on Two Criteria VBA or Formula DESCRIPTION See Mr Excel and excelisfun Extract Multiple Records Based on Two Criteria using VBA and formula. See the functions AND, SUM, VLOOKUP, ROWS, COLUMNS [Read More >>>]