Calculated

Excel 2013 PowerPivot Basics #09: Calculated Fields & DAX Functions 9 Examples

• Report rights infringement
• published: 26 Oct 2014
• views: 59322

Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how to create 9 different DAX functions or Calculated Fields 1. (00:08 min mark) Overview of Reports we need to create 2. (01:05 min mark) Calculated Fields Overview 3. (01:58 min mark) 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) 4. (03:22 min mark) Reminder of “Row Context” 5. (04:47 min mark) DAX function = Data Analysis EXpressions 6. (05:05 min mark) Calculated Field 1: Total Net Revenue:=SUM(fSales[Net Revenue]). 7. (06:59 min mark) Calculated Field 2: Total COGS:=SUMX(fSales,ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units],2)) 8. (07:24 min mark) Calculated Field or Calculated Column? Which one to use? 9. (09:05 min mark) “Row Context” in Calculated Field, use X functions like SUMX and COUNTX. 10. (10:55 min mark) Calculated Field 3: GrossProfit:=[TotalNetRevenue]-[TotalCOGS] 11. (10:55 min mark) 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. 12. (11:45 min mark) Edit Calculated Fields in Measure Grid. 13. (12:25 min mark) Calculated Field 4: GrossProfitPercentage:=DIVIDE([GrossProfit],[TotalNetRevenue]) 14. (13:42 min mark) 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). 15. (16:30 min mark) 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. 16. (17:35 min mark) Calculated Field 5: DISTINCTCOUNT function to count actual days where sales were made. Number Of Sales Days:=DISTINCTCOUNT(fSales[Date]) 17. (18:45 min mark) Calculated Field 6: Total Number of Days Over 4 Year Period:=COUNTROWS(dCalendar) 18. (19:40 min mark) Calculated Field 7: Average Sales per Sales Day:=ROUND(DIVIDE([Total Net Revenue],[Number of Sales days]),2) 19. (20:34 min mark) Start Report 2: Distinct Count and Ave. Sales per day by YEAR. 20. (21:47 min mark) Compare Excel Formula and DAX formula (Calculated Field) for Percentage Change. See IFERROR in Excel and how it can handle two data types. 21. (24:00 min mark) Calculated Field 8: Total Net Revenue From Last Year:=CALCULATE([Total Net Revenue],SAMEPERIODLASTYEAR(dCalendar[Dates])). 22. (24:00 min mark) CALCULATE will allows us to change the Filter Context. 23. (26:08 min mark) % Change for Net Revenue:=IF([Total Net Revenue From Last Year]=0,BLANK(),[Total Net Revenue]/[Total Net Revenue From Last Year]-1) 24. (27:50 min mark) Look at IFERROR 25. (28:40 min mark) IF rather than IFERROR 26. (28:05 min mark) BLANK function (add annotation) 27. (28:59 min mark) Conclusion In PowerPivot: BLANK represents Empty Cell, Missing Value, Null (Database "unknown value") BLANK = Empty Cell, Missing Value BLANK is not a Zero Length Text String BLANK is not an Error