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

## Leave a Reply

You must be logged in to post a comment.