# Excel Magic Trick 1366

## Power BI Desktop: Build Data Model, Get Data, DAX Formulas, Visualizations, Publish 2 Web

### 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