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

Obtain file: http://folks.highline.edu/mgirvin/excelisfun.htm Scroll to all the way down to PowerPivot Part.

See the best way to create 9 totally different DAX capabilities or Calculated Fields
1. (00:08 min mark) Overview of Reviews we have to create
2. (01:05 min mark) Calculated Fields Overview
three. (01:58 min mark) Calculated Column For Web Income utilizing ROUND and RELATED perform and desk names and columns names with multiplication operators. Web Income
four. (03:22 min mark) Reminder of “Row Context”
5. (04:47 min mark) DAX perform = Knowledge Evaluation EXpressions
6. (05:05 min mark) Calculated Area 1: Complete Web Income:=SUM(fSales[Net Revenue]).
7. (06:59 min mark) Calculated Area 2: Complete COGS:=SUMX(fSales,ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units],2))
eight. (07:24 min mark) Calculated Area or Calculated Column? Which one to make use of?
9. (09:05 min mark) “Row Context” in Calculated Area, use X capabilities like SUMX and COUNTX.
10. (10:55 min mark) Calculated Area three: GrossProfit:=[TotalNetRevenue]-[TotalCOGS]
11. (10:55 min mark) First use of Calculated Area in one other calculated subject: we’re certain to make use of our conference of Sq. Brackets signifies that it’s a Calculated Area!!!! And never a column title.
12. (11:45 min mark) Edit Calculated Fields in Measure Grid.
13. (12:25 min mark) Calculated Area four: GrossProfitPercentage:=DIVIDE([GrossProfit],[TotalNetRevenue])
14. (13:42 min mark) First Report: Web income, COGS, Gross Revenue, GP % formulation with 4 Standards: Yea, Month, Product and Coloration Standards (Product and Coloration on Slicer, Yr and Month in PivotTable Row and Column Headers).
15. (16:30 min mark) Look at what “Filter Context” means from a cell in PivotTable with 4 standards. Take a look at Formulation and Design View to see relationships between tables to attempt to perceive of the filtering of the Columnar database works primarily based on the Standards that’s dropped into the PivotTable.
16. (17:35 min mark) Calculated Area 5: DISTINCTCOUNT perform to depend precise days the place gross sales had been made. Quantity Of Gross sales Days:=DISTINCTCOUNT(fSales[Date])
17. (18:45 min mark) Calculated Area 6: Complete Variety of Days Over four Yr Interval:=COUNTROWS(dCalendar)
18. (19:40 min mark) Calculated Area 7: Common Gross sales per Gross sales Day:=ROUND(DIVIDE([Total Net Revenue],[Number of Sales days]),2)
19. (20:34 min mark) Begin Report 2: Distinct Depend and Ave. Gross sales per day by YEAR.
20. (21:47 min mark) Examine Excel Formulation and DAX components (Calculated Area) for Share Change. See IFERROR in Excel and the way it can deal with two information sorts.
21. (24:00 min mark) Calculated Area eight: Complete Web Income From Final Yr:=CALCULATE([Total Net Revenue],SAMEPERIODLASTYEAR(dCalendar[Dates])).
22. (24:00 min mark) CALCULATE will permits us to vary the Filter Context.
23. (26:08 min mark) % Change for Web Income:=IF([Total Net Revenue From Last Year]=zero,BLANK(),[Total Net Revenue]/[Total Net Revenue From Last Year]-1)
24. (27:50 min mark) Take a look at IFERROR
25. (28:40 min mark) IF quite than IFERROR
26. (28:05 min mark) BLANK perform (add annotation)
27. (28:59 min mark) Conclusion

In PowerPivot:
BLANK represents Empty Cell, Lacking Worth, Null (Database “unknown worth”)
BLANK = Empty Cell, Lacking Worth
BLANK isn’t a Zero Size Textual content String
BLANK isn’t an Error


You might be interested in


Your email address will not be published. Required fields are marked *