Excel 2013 PowerPivot Basics 09

Calculated Fields & DAX Functions 9 Examples

DESCRIPTION

See how to create 9 different DAX functions or Calculated Fields
(00:08) Overview of Reports we need to create
(01:05) Calculated Fields Overview
(01:58) Calculated Column For Net Revenue using ROUND and RELATED function and table names and columns names with multiplication operators. Net Revenue
=ROUND(RELATED(dProducts[RetailPrice])*(­1-fSales[RevenueDiscount])*fSales[Units]­,2)
(03:22) Reminder of “Row Context”
(04:47) DAX function = Data Analysis EXpressions
(05:05) Calculated Field 1: Total Net Revenue:=SUM(fSales[Net Revenue]).
(06:59) Calculated Field 2: Total COGS:=SUMX(fSales,ROUND(RELATED(dProduct­s[StandardCost])*fSales[PercentOfStandar­dCost]*fSales[Units],2))
(07:24) Calculated Field or Calculated Column? Which one to use?
(09:05) “Row Context” in Calculated Field, use X functions like SUMX and COUNTX.
(10:55) Calculated Field 3: GrossProfit:=[TotalNetRevenue]-[TotalCOG­S] (10:55) First use of Calculated Field in another calculated field: we are sure to use our convention of Square Brackets means that it is a Calculated Field!!!! And not a column name.
(11:45) Edit Calculated Fields in Measure Grid.
(12:25) Calculated Field 4: GrossProfitPercentage:=DIVIDE([GrossProf­it],[TotalNetRevenue])
(13:42) First Report: Net revenue, COGS, Gross Profit, GP % formulas with Four Criteria: Yea, Month, Product and Color Criteria (Product and Color on Slicer, Year and Month in PivotTable Row and Column Headers).
(16:30) Examine what “Filter Context” means from a cell in PivotTable with Four criteria. Look at Formulas and Design View to see relationships between tables to try and understand of the filtering of the Columnar database works based on the Criteria that is dropped into the PivotTable.
(17:35) Calculated Field 5: DISTINCTCOUNT function to count actual days where sales were made. Number Of Sales Days:=DISTINCTCOUNT(fSales[Date])
(18:45) Calculated Field 6: Total Number of Days Over 4 Year Period:=COUNTROWS(dCalendar)
(19:40) Calculated Field 7: Average Sales per Sales Day:=ROUND(DIVIDE([Total Net Revenue],[Number of Sales days]),2)
(20:34) Start Report 2: Distinct Count and Ave. Sales per day by YEAR.
(21:47) Compare Excel Formula and DAX formula (Calculated Field) for Percentage Change. See IFERROR in Excel and how it can handle two data types.
(24:00) Calculated Field 8: Total Net Revenue From Last Year:=CALCULATE([Total Net Revenue],SAMEPERIODLASTYEAR(dCalendar[Da­tes])).
(24:00) CALCULATE will allows us to change the Filter Context.
(26:08) % Change for Net Revenue:=IF([Total Net Revenue From Last Year]=0,BLANK(),[Total Net Revenue]/[Total Net Revenue From Last Year]-1)
(27:50) Look at IFERROR
(28:40) IF rather than IFERROR
(28:05) BLANK function (add annotation)
(28:59) Conclusion

WORKBOOKS