SSWUG Expo Part 1 Getting Started with PowerPivot
Getting started with PowerPivot
[email protected]
Twitter: donalddotfarmer
The Business of Analytics
Analysts and IT
Analytic Technologies
Radical Familiarity
PowerPivot Technologies
demo querying enterprise data
demo desktop data
Excel Functions in PowerPivot Date and Time
Information
DATE
ISBLANK
DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NETWORKDAYS NETWORKDAYS.INTL NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM WORKDAY WORKDAY.INTL YEAR YEARFRAC
ISERROR ISLOGICAL ISNONTEXT ISNUMBER ISTEXT
Logical AND IF IFERROR NOT OR FALSE TRUE
Math and Trig ABS CEILING, ISO.CEILING EXP FACT FLOOR INT LN LOG LOG10 MOD MROUND PI POWER QUOTIENT RAND RANDBETWEEN ROUND ROUNDDOWN ROUNDUP SIGN SQRT SUM SUMSQ TRUNC
Statistical
Text
AVERAGE
CHAR
AVERAGEA COUNT COUNTA COUNTBLANK MAX MAXA MIN MINA STDEV.S STDEVA STDEV.P STDEVPA VAR.S VARA VAR.P VARPA
CODE CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TEXT TRIM UPPER VALUE
Lookup Functions o LookupValue (ResultColumn, SearchColumn, SearchValue...) o Related (Column) o RelatedTable (Table)
59
Aggregation Functions o SUMX (Table, Expression) o AVERAGEX (Table, Expression) o COUNTAX (Table, Expression)
o MINX (Table, Expression) o MAXX (Table, Expression) o EARLIER(Column) o EARLIEST(Column) 60
Table Functions o RelatedTable (Table) o Filter (Table, Condition) o Distinct (Column) o Values (Column) o All (Table), All(Column) o AllExcept (Table, Col1, Col2,...)
61
Variants of Calculate() Scalar functions o Calculate o CalculateAll o CalculateAllRows Table Functions o CalculateTable o CalculateTableAll o CalculateTableAllRows Supporting Functions o All o AllExcept o AllNoBlankRow 62
Time Intelligence Functions o o o o o o o o o o o o o o o o o
FirstDate LastDate FirstNonBlank LastNonBlank StartofMonth StartofYear EndofMonth EndofQuarter EndofYear DateAdd DatesBetween DatesInPeriod ParallelPeriod PreviousDay PreviousMonth PreviousQuarter PreviousYear
o o o o o o o o o o o o o o o o o
NextDay NextMonth NextQuarter NextYear DatesMTD DatesQTD DatesYTD SamePeriodLastYear TotalMTD TotalQTD TotalYTD OpeningBalanceMonth OpeningBalanceQuarter OpeningBalanceYear ClosingBalanceMonth ClosingBalanceQuarter ClosingBalanceYear
demo reports and expressions
demo publishing
demo IT Dashboard
PowerPivot PowerPivot add-in for Excel
PowerPivot add-in for SharePoint
Resources www.powerpivot.com www.powerpivotpro.com www.powerpivotgeek.com www.powerpivottwins.com Twitter #powerpivot @powerpivot @powerpivotpro
Picture Credits The Advertising Archives Bavarian State Library Donald Farmer’s private collection Science and Society Picture Library
[email protected]
Twitter: donalddotfarmer
The Business of Analytics
Analysts and IT
Analytic Technologies
Radical Familiarity
PowerPivot Technologies
demo querying enterprise data
demo desktop data
Excel Functions in PowerPivot Date and Time
Information
DATE
ISBLANK
DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NETWORKDAYS NETWORKDAYS.INTL NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM WORKDAY WORKDAY.INTL YEAR YEARFRAC
ISERROR ISLOGICAL ISNONTEXT ISNUMBER ISTEXT
Logical AND IF IFERROR NOT OR FALSE TRUE
Math and Trig ABS CEILING, ISO.CEILING EXP FACT FLOOR INT LN LOG LOG10 MOD MROUND PI POWER QUOTIENT RAND RANDBETWEEN ROUND ROUNDDOWN ROUNDUP SIGN SQRT SUM SUMSQ TRUNC
Statistical
Text
AVERAGE
CHAR
AVERAGEA COUNT COUNTA COUNTBLANK MAX MAXA MIN MINA STDEV.S STDEVA STDEV.P STDEVPA VAR.S VARA VAR.P VARPA
CODE CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TEXT TRIM UPPER VALUE
Lookup Functions o LookupValue (ResultColumn, SearchColumn, SearchValue...) o Related (Column) o RelatedTable (Table)
59
Aggregation Functions o SUMX (Table, Expression) o AVERAGEX (Table, Expression) o COUNTAX (Table, Expression)
o MINX (Table, Expression) o MAXX (Table, Expression) o EARLIER(Column) o EARLIEST(Column) 60
Table Functions o RelatedTable (Table) o Filter (Table, Condition) o Distinct (Column) o Values (Column) o All (Table), All(Column) o AllExcept (Table, Col1, Col2,...)
61
Variants of Calculate() Scalar functions o Calculate o CalculateAll o CalculateAllRows Table Functions o CalculateTable o CalculateTableAll o CalculateTableAllRows Supporting Functions o All o AllExcept o AllNoBlankRow 62
Time Intelligence Functions o o o o o o o o o o o o o o o o o
FirstDate LastDate FirstNonBlank LastNonBlank StartofMonth StartofYear EndofMonth EndofQuarter EndofYear DateAdd DatesBetween DatesInPeriod ParallelPeriod PreviousDay PreviousMonth PreviousQuarter PreviousYear
o o o o o o o o o o o o o o o o o
NextDay NextMonth NextQuarter NextYear DatesMTD DatesQTD DatesYTD SamePeriodLastYear TotalMTD TotalQTD TotalYTD OpeningBalanceMonth OpeningBalanceQuarter OpeningBalanceYear ClosingBalanceMonth ClosingBalanceQuarter ClosingBalanceYear
demo reports and expressions
demo publishing
demo IT Dashboard
PowerPivot PowerPivot add-in for Excel
PowerPivot add-in for SharePoint
Resources www.powerpivot.com www.powerpivotpro.com www.powerpivotgeek.com www.powerpivottwins.com Twitter #powerpivot @powerpivot @powerpivotpro
Picture Credits The Advertising Archives Bavarian State Library Donald Farmer’s private collection Science and Society Picture Library
