7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 1/15

Tutorial: PivotTable data analysis using a Data Model in Excel 2013

In less than an hour, you can build a PivotTable reort in Excel that co!bines data "ro! !ultiletables# The "irst art o" this tutorial stes you through data i!ort and exloration# In the secondhal", you$ll use the Po%er Pivot add&in to re"ine the data !odel that lives behind the reort, learningho% to add calculations, hierarchies, and oti!i'ations "or Po%er (ie% reorting#

I!ort so!e data

)e"ore you can create a PivotTable, you*ll need so!e data# +et*s get so!e sales data "ro! thesa!le database you do%nloaded#

1# Do%nload sa!le data -ontoso(2. "or this tutorial# /ee et sa!le data "or D andData Model tutorials "or details# Extract and save the data "iles in a location that$s easilyaccessible, such as Do%nloads or My Docu!ents#

2# In Excel, oen a blan %orboo#

3# -lic Data 4 Get External Data 4 From Access#

5# o to the "older that contains the sa!le data "iles and select ContosoSales#

6# -lic Open# )ecause you are connecting to a database "ile that contains !ultile tables,the Select Table dialog aears so that you can choose %hich tables to i!ort#

7# In /elect Table, chec Enable selection of multiple tables#

8# -hoose all o" the tables and clic OK#

9# In I!ort Data, clic PivotTable Report and clic OK#

hat haened;

<ou !ight not have reali'ed it yet, but you$ve =ust created a data !odel# It*s created auto!atically%hen you i!ort or %or %ith !ultile tables si!ultaneously in the sa!e PivotTable reort# The!odel is !ostly transarent in Excel, but you can vie% and !odi"y it directly using the Po%er Pivotadd&in# In Excel, the resence o" a data !odel is evident %hen you see a collection o" tables in thePivotTable >ields list# There are several %ays to create a !odel# /ee -reate a Data Model in Excel "or details#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 2/15

Exlore data using a PivotTable

Exloring data is easy %hen you drag "ields to the Values, Columns, and Ros areas on thePivotTable "ield list#

1# In the "ield list, scroll do%n until you "ind the >act/ales table#

2# -lic /ales!ount# )ecause this data is nu!eric, Excel auto!atically laces /ales!ountin the (alues area#

3# In Di!Date, drag -alendar<ear to -olu!ns#

5# In Di!Product/ubcategory, drag Product/ubcategory?a!e to @o%s#

6# In Di!Product, drag )rand?a!e to @o%s, lacing it beneath subcategory#

<our PivotTable should loo si!ilar to the "ollo%ing screen#

ith !ini!al e""ort, you no% have a basic PivotTable that includes "ields "ro! "our di""erent tables#hat !ade this tas so si!le %ere the re&existing relationshis a!ong the tables# )ecause tablerelationshis existed in the source, and because you i!orted all the tables in a single oeration,Excel could recreate those relationshis in the !odel#

)ut %hat i" your data originates "ro! di""erent sources, or is i!orted at a later ti!e; Tyically, youcan incororate ne% data by creating relationshis based on !atching colu!ns# In the next ste,you$ll i!ort additional tables and learn the reAuire!ents and stes "or creating ne% relationshis#

 dd !ore tables

+earning ho% to set u table relationshis reAuires that you have so!e additional, unconnectedtables to %or %ith# In this ste, you$ll get the re!aining data used in this tutorial by i!orting oneadditional database "ile and asting data "ro! t%o other %orboos#

 dd roduct categories

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 3/15

1# In the %orboo oen a ne% sheet# <ou$ll use it to store additional data#

2# -lic Data 4 Get External Data 4 From Access#

3# o to the "older that contains the sa!le data "iles and select Product-ategories# -licOpen#

5# In I!ort Data, select Table, and clic OK#

 dd geograhy data

1# Insert another sheet#

2# >ro! the sa!le data "iles, oen eograhy#xlsx, lace the cursor in 1, and then ress-trl&/hi"t&End to select all o" the data#

3# -oy the data to the cliboard#

5# Paste the data into the e!ty sheet you =ust added#

6# -lic Format as Table, choosing any style# >or!atting the data as a table lets you na!e it,%hich %ill co!e in handy %hen you de"ine relationshis in a later ste#

7# In >or!at s Table, veri"y that !" table #as #ea$ers is selected# -lic OK#

8# ?a!e the table eograhy# In Table Tools 4 Desi%n, tye eograhy in Table ?a!e#

9# -lose eograhy#xlsx to clear it "ro! your %orsace#

 dd store data

• @eeat the revious stes "or the /tores#xlsx "ile, asting its contents into an e!ty sheet#

?a!e the table /tores#

<ou should no% have "our sheets# /heet1 contains the PivotTable, /heet2 containsPro$uctCate%ories , /heet3 contains eograhy, and /heet5 contains /tores# )ecause you toothe ti!e to na!e each table, the next ste, creating relationshis, %ill be !uch si!ler#

Bse "ields "ro! the ne%ly i!orted tables

<ou can i!!ediately begin using "ields "ro! the tables you =ust i!orted# I" Excel cannot deter!ineho% to incororate a "ield into the PivotTable reort, you$ll be ased to create a table relationshithat associates the ne% table %ith one that is already art o" the !odel#

1# t the to o" PivotTable >ields, clic All to vie% the co!lete list o" available tables#

2# /croll to the botto! o" the list# That$s %here you$ll "ind the ne% tables you =ust added#

3# Exand /tores#

5# Drag /tore?a!e to the >ilters area#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 4/15

6# ?otice that Excel ro!ts you to create a relationshi# This noti"ication occurs becauseyou$ve used "ields "ro! a table that is unrelated to the !odel#

7# -lic Create to oen the -reate @elationshi dialog#

8# In Table, choose >act/ales# In the sa!le data you$re using, >act/ales contains detailed

sales and cost in"or!ation about -ontoso$s business, as %ell as eys to other tables,including store codes that are also resent in the /tores#xlsx "ile you i!orted in therevious ste#

9# In -olu!n >oreign., choose /toreCey#

# In @elated Table, choose /tores#

10# In @elated -olu!n Pri!ary., choose /toreCey#

11# -lic OK#

)ehind the scenes, Excel is building a Data Model that can be used throughout the %orboo in anynu!ber o" PivotTables, Pivot-harts, or Po%er (ie% reorts# >unda!ental to this !odel are tablerelationshis that deter!ine navigation and calculation aths used in a PivotTable reort# In the nexttas, you$ll create relationshis !anually to connect the data you =ust i!orted#

 dd relationshis

<ou can syste!atically create table relationshis "or all ne% tables that you i!ort# I" you$re sharingthe %orboo %ith colleagues, having rede"ined relationshis %ill be areciated i" they don$t no%the data as %ell as you do#

hen creating relationshis !anually, you %ill %or %ith t%o tables at a ti!e# >or each table, you$llchoose colu!ns that tell Excel ho% to loo u related ro%s in another table#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 5/15

@elate Product/ubcategory to Product-ategory

1# In Excel, clic Data 4 Relations#ips 4 &e#

2# In Table, choose Di!Product/ubcategory#

3# In -olu!n >oreign., choose Product-ategoryCey#

5# In @elated Table, choose TableProduct-ategory#accdb#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 6/15

6# In @elated -olu!n Pri!ary., choose Product-ategoryCey#

7# -lic OK#

8# -lose the !ana%e Relations#ips dialog#

 dd categories to the PivotTable

 lthough the Data Model has been udated to include additional tables and relationshis, thePivotTable isn$t using the! yet# In this tas, you$ll add Product-ategory to the PivotTable >ields list#

1# In PivotTable >ields, clic All to sho% tables that exist in the Data Model#

2# /croll to the botto! o" the list#

3# In the @o%s area, re!ove )rand?a!e#

5# Exand TableDi!Product-ategories#accdb#

6# Drag Product-ategory?a!e to the @o%s area, lacing it above Product/ubcategory#

7# In PivotTable >ields, clic Active to veri"y that the tables you =ust used are no% activelyused in the PivotTable#

-hecoint: @evie% %hat you learned

<ou no% have a PivotTable that includes data "ro! !ultile tables, several o" %hich you i!orted ina subseAuent ste# To ull this data together, you had to create table relationshis that Excel usesto correlate the ro%s# <ou learned that having colu!ns that rovide !atching data is essential "orlooing u related ro%s# In the sa!le data "iles, all o" the tables include a colu!n that can be used"or this urose#

 lthough the PivotTable is "unctional, you$ve robably noticed several things that could bei!roved# The PivotTable >ields +ist see!s to have extra tables Di!Entity. and colu!nsET++oadID. that are unrelated to -ontoso$s business# nd, %e still haven$t integrated theeograhy data#

?ext u: (ie% and extend your !odel %ith Po%er Pivot

In the next series o" tass, you$ll use the Microso"t F""ice Po%er Pivot in Microso"t Excel 2013 add&in to extend the !odel# <ou$ll "ind that you can create relationshis !ore easily using the Diagra!(ie% the add&in rovides# <ou$ll also use the add&in to create calculations and hierarchies, hideite!s that shouldn$t be aearing in the "ield list, and oti!i'e the data "or additional reorting#

&ote The Po%er Pivot in Microso"t Excel 2013 add&in is available in F""ice Pro"essional Plus# /eePo%er Pivot in Microso"t Excel 2013 add&in "or !ore in"or!ation#

 dd Poer Pivot to the Excel ribbon by enabling the Po%er Pivot add&in#

1# o to File 4 Options 4 A$$'(ns#

2# In the !ana%e box, clic CO! A$$'ins4 Go#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 7/15

3# -hec the !icrosoft Office Poer Pivot in !icrosoft Excel )*+, box, and then clic OK#

The ribbon no% has a Po%er Pivot tab#

 dd a relationshi using Diagra! (ie% in Po%er Pivot

1# In Excel, clic /heet3 to !ae it the active sheet# /heet3 contains the eograhy table youi!orted earlier#

2# Fn the ribbon, clic Poer Pivot 4 A$$ to Data !o$el# This ste adds the eograhytable to the !odel# It also oens the Po%er Pivot add&in, %hich you$ll use to er"or! there!aining stes in this tas#

3# ?otice that the Po%er Pivot %indo% sho%s all the tables in the !odel, including eograhy#-lic through a coule o" tables# In the add&in, you can vie% all o" the data that your !odelcontains#

5# In the Po%er Pivot %indo%, in the (ie% section, clic Dia%ram Vie#

6# Bse the slide bar to resi'e the diagra! so that you can see all ob=ects in the diagra!#?otice that t%o tables are unrelated to the rest o" the diagra!: Di!Entity and eograhy#

7# @ight&clic Di!Entity and clic Delete# This table is an arti"act "ro! the original databaseand is not needed in the !odel#

8# Goo! in on eograhy so that you can vie% all o" its "ields# <ou can use the slider to !aethe table diagra! bigger#

9# ?otice eograhy has eograhyCey# This colu!n contains values that uniAuely identi"yeach ro% in the eograhy table# +et$s "ind out i" other tables in the !odel also use this ey#I" they do, %e can create a relationshi that connects eograhy to the rest o" the !odel#

# -lic Fin$#

10# In >ind Metadata, tye eograhyCey#

11# -lic Fin$ &ext several ti!es# <ou$ll notice the eograhyCey sho%s u in the eograhytable and in the /tores table#

12# @eosition the eograhy table so that it is next to /tores#

13# Drag the eograhyCey colu!n in /tores to the eograhyCey colu!n in eograhy#Po%er Pivot dra%s a line bet%een the t%o colu!ns, indicating the relationshi#

In this tas, you learned a ne% techniAue "or adding tables and creating relationshis# <ou no%have a "ully integrated !odel, %ith all tables connected and available to the PivotTable in /heet1#

Tip In Diagra! (ie%, several table diagra!s are "ully extended, sho%ing colu!ns lieET++oadID, +oadDate, and BdateDate# These articular "ields are arti"acts "ro! the original-ontoso data %arehouse, added to suort data extraction and loading oerations# <ou don$t needthe! in your !odel# To get rid o" the!, highlight and right&clic the "ield, and clic Delete#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 8/15

-reate a calculated colu!n

In Po%er Pivot, you can use Data nalysis Exressions D. to add calculations# In this tas, you$llcalculate total ro"it, and add calculated colu!n that re"erences data values "ro! other tables#+ater, you$ll see ho% to use re"erenced colu!ns to si!li"y your !odel#

1# In the Po%er Pivot %indo%, s%itch bac to Data (ie%#

2# @ena!e the TableProduct-ategories accdb table to a "riendlier na!e# <ou$ll bere"erencing this table in the "ollo%ing stes and a shorter na!e %ill !ae the calculationseasier to read# @ight&clic the table na!e, clic Rename, tye Product-ategories, and thenress Enter#

3# /elect the >act/ales table#

5# -lic Desi%n 4 Columns 4 A$$#

6# In the "or!ula bar above the table, tye the "ollo%ing "or!ula# uto-o!lete hels you tyethe "ully Auali"ied na!es o" colu!ns and tables, and lists the "unctions that are available#<ou can also =ust clic the colu!n and Po%er Pivot adds the colu!n na!e to the "or!ula#

H /ales!ountJ & Total-ostJ & @eturn!ountJ

7# hen you have "inished building the "or!ula, ress Enter to accet the "or!ula#

(alues are oulated "or all the ro%s in the calculated colu!n# I" you scroll do%n throughthe table, you %ill see that ro%s can have di""erent values "or this colu!n, based on thedata that is in each ro%#

8# @ena!e the colu!n by right&clicing -alculated-olu!n1 and selecting Rename Column#Tye Pro"it, and then ress Enter#

9# ?o% select the Di!Product table#

# -lic Desi%n 4 Columns 4 A$$#

10# In the "or!ula bar above the table, tye the "ollo%ing "or!ula#

H @E+TEDProduct-ategoriesProduct-ategory?a!eJ.

The @E+TED "unction returns a value "ro! a related table# In this case, theProduct-ategories table includes the na!es o" roduct categories, %hich %ill be use"ul tohave in the Di!Product table %hen you build a hierarchy that includes category in"or!ation#

>or !ore in"or!ation about this "unction, see @E+TED >unction D.#

11# hen you have "inished building the "or!ula, ress Enter to accet the "or!ula#

(alues are oulated "or all the ro%s in the calculated colu!n# I" you scroll do%n throughthe table, you %ill see that each ro% no% has a Product -ategory ?a!e#

12# @ena!e the colu!n by right&clicing -alculated-olu!n1 and selecting Rename Column#Tye Product-ategory, and then ress Enter#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 9/15

13# -lic Desi%n 4 Columns 4 A$$#

15# In the "or!ula bar above the table, tye the "ollo%ing "or!ula, and then ress Enter toaccet the "or!ula#

H @E+TEDDi!Product/ubcategoryProduct/ubcategory?a!eJ.

16# @ena!e the colu!n by right&clicing -alculated-olu!n1 and selecting Rename Column#Tye Product/ubcategory, and then ress Enter#

-reate a hierarchy

Most !odels include data that is inherently hierarchical# -o!!on exa!les include calendar data,geograhical data, and roduct categories# -reating hierarchies are use"ul because you can dragone ite! the hierarchy. to a reort instead o" having to asse!ble and order the sa!e "ields overand over#

1# In Po%er Pivot, s%itch to Diagra! (ie%# Exand the Di!Date table so that you can !oreeasily see all o" its "ields#

2# Press and hold -trl and clic the -alendar<ear, -alendarKuarter and -alendarMonthcolu!ns you %ill need to scroll do%n the table.#

3# ith the three colu!ns selected, right&clic one o" the! and clic Create -ierarc#"# arent hierarchy node, Lierarchy 1, is created at the botto! o" the table, and the selectedcolu!ns are coied under the hierarchy as child nodes#

5# Tye Dates as the na!e "or your ne% hierarchy#

6# dd the >ullDate+abel colu!n to the hierarchy# @ight&clic >ullDate+abel and select A$$ to-ierarc#"# -hoose Date# >ullDate+abel contains a "ull date, including year, !onth and day#

(eri"y that >ullDate+abel aears last in the hierarchy# <ou no% have !ultilevel hierarchythat includes year, Auarter, !onth, and individual calendar days#

7# /till in Diagra! (ie%, oint to the Di!Product table, and then clic the Create -ierarc#" button in the table header# n e!ty hierarchy arent node aears at the botto! o" thetable#

8# Tye Product -ategories as the na!e "or your ne% hierarchy#

9# To create hierarchy child nodes, drag the Product-ategory and Product/ubcategory ontothe hierarchy#

# @ight&clic Product?a!e and select A$$ to -ierarc#"# -hoose Product -ategories#

?o% that you no% a coule o" di""erent %ays to create a hierarchy, let$s use the! in the PivotTable#

1# o bac to Excel#

2# In /heet1 the sheet that contains the PivotTable., re!ove the "ields in the @o%s area#

3# @elace the! %ith the ne% Product -ategories hierarchy in Di!Product#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 10/15

5# /i!ilarly, relace -alendar<ear in the -olu!ns area %ith the Dates hierarchy in Di!Date#

hen you exlore the data no%, it$s easy to see the bene"its o" using hierarchies# <ou canindeendently exand and close di""erent areas o" the PivotTable, roviding !ore control over ho%available sace is used# >urther!ore, by adding a single hierarchy to both @o%s and -olu!ns, youget rich and i!!ediate drill do%n, %ithout having to stac !ultile "ields to get a si!ilar e""ect#

Lide colu!ns

?o% that you$ve created a Product -ategories hierarchy and laced it in Di!Product, you no longerneed Di!Product-ategory or Di!Product/ubcategory in the PivotTable >ields list# In this tas,you$ll learn ho% to hide extraneous tables and colu!ns that are taing u roo! in the PivotTable>ields list# )y hiding the tables and colu!ns, you i!rove the reorting exerience %ithout a""ectingthe !odel that rovides data relationshis and calculations#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 11/15

<ou can hide individual colu!ns, a range o" colu!ns, or a %hole table# Table and colu!n na!es

are grayed out to re"lect that it is hidden to reorting clients that consu!e the !odel# Liddencolu!ns are grayed out in the !odel to indicate their state, but re!ain visible in the Data (ie% sothat you can continue to %or %ith the!#

1# In Po%er Pivot, !ae sure Data (ie% is selected#

2# In the tabs at the botto!, right clic Di!Product/ubcategory and select -i$e from ClientTools#

3# @eeat "or Product-ategories#

5# Fen Di!Product#

6# @ight&clic the "ollo%ing colu!ns and clic -i$e from Client Tools:

o ProductCey

o Product+abel

o Product/ubcategory

7# Multi&select ad=acent colu!ns# /tart %ith -lassID and continue to Product/ubcategory atthe end# @ight&clic to hide the!#

8# @eeat "or other tables, re!oving IDs, eys, or other details that you %on$t use in thisreort#

/%itch bac to Excel to /heet 1 %ith the PivotTable >ields list to see the di""erence# The nu!ber o"tables is reduced and Di!Product includes only those ite!s that you$re !ore liely to use %henanaly'ing sales#

-reate a Po%er (ie% reort

PivotTable reorts are not the only tye o" reort that bene"its "ro! a Data Model# Bsing the sa!e!odel you =ust built, you can add a Po%er (ie% sheet to try out so!e o" the layouts it rovides#

1# In Excel, clic (nsert 4 Poer Vie#

&ote I" this is the "irst ti!e you$re using Po%er (ie% on this !achine, you$re ro!ted toenable the add&in and install /ilverlight "irst#

2# In Po%er (ie% >ields, clic the arro% next to the >act/ales table, and clic /ales!ount#

3# Exand the eograhy table and clic @egion-ountry?a!e#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 12/15

5# In the ribbon, clic !ap#

6# !a reort aears# Drag a corner to resi'e it# Fn the !a, blue circles o" varying si'eindicate sales er"or!ance "or di""erent countries or regions#

Fti!i'e "or Po%er (ie% reorting

Maing a "e% s!all changes to your !odel %ill result in !ore intuitive resonses %hen designing aPo%er (ie% reort# In this tas, you %ill add %eb site B@+s "or several !anu"acturers, and thencategori'e that data as a eb B@+ so the B@+ address sho%s u as a lin#

 s a "irst ste, add B@+s to your %orboo#

1# In Excel, oen a ne% sheet and coy these values:

Manu"acturerB@+ Manu"acturerID

htt:%%%#contoso#co! -ontoso, +TD

htt:%%%#adventure&%ors#co! dventure ors

htt:%%%#"abria!#co! >abria!, Inc#

1# >or!at the cells as a table, and then na!e the table B@+#

2# -reate a relationshi bet%een B@+ and the table that contains !anu"acturer na!es,Di!Product:

a# -lic Data 4 Relations#ips# The -reate @elationshi dialog aears#

b# -lic &e#

c# In Table, select Di!Product#

d# In -olu!n, select Manu"acturer#

e# In @elated Table, select B@+#

"# In @elated -olu!n Pri!ary., select Manu"acturerID#

To co!are be"ore and a"ter results, start a ne% Po%er (ie% reort and add >act/ales N/ales!ount, di!Product N Manu"acturer, and B@+ N Manu"acturerB@+ to a reort# ?otice that theB@+s sho% u as static text#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 13/15

@endering a B@+ as an active hyerlin reAuires categori'ation# To categori'e a colu!n, you$ll usePo%er Pivot#

1# In Po%er Pivot, oen B@+#

2# /elect Manu"acturerB@+#

3# -lic A$vance$ 4 Reportin% Properties 4 Data Cate%or". /ncate%ori0e$#

5# -lic the do%n arro%#

6# /elect eb B@+#

7# In Excel, clic (nsert 4 Poer Vie#

8# In Po%er (ie% >ields, select >act/ales N /ales!ount, di!Product N Manu"acturer, andB@+ N Manu"acturerB@+# This ti!e, the B@+s sho% u as actual hyerlins#

Fther Po%er (ie% oti!i'ations include de"ining a de"ault "ield set "or each table and settingroerties that deter!ine %hether ro%s o" reeating data are aggregated or listed indeendently#/ee -on"igure de"ault "ield set "or Po%er (ie% reorts and -on"igure table behavior roerties "orPo%er (ie% reorts "or !ore in"or!ation#

-reate calculated "ields

In the second tas, Exlore data using a PivotTable, you cliced on the /ales!ount "ield in thePivotTable >ields list# )ecause /ales!ount is a nu!eric colu!n, it %as auto!atically laced intothe (alues area o" the PivotTable# /u! o" /ales!ount %as then ready to calculate sales a!ounts"or %hatever "ilters are to be alied# In this case, no "ilters at "irst, but then -alendar<ear,Product/ubcategory?a!e, and )rand?a!e#

hat you really did %as create an i!licit calculated "ield, !aing it easy to analy'e sales a!ounts"ro! the >act/ales table against other "ields such as roduct category, region, and dates# I!licitcalculated "ields are created by Excel %hen you drag a "ield to the (alues area or %hen you clic ona nu!eric "ield, lie you did %ith /ales!ount# I!licit calculated "ields are "or!ulas that usestandard aggregation "unctions such as /BM, -FB?T, and (E@E, created auto!atically "oryou#

There are other tyes o" calculated "ields too# <ou can create exlicit calculated "ields in Po%erPivot# Bnlie an i!licit calculated "ield, %hich can only be used in the PivotTable they %erecreated, exlicit calculated "ields can be used in any PivotTable in the %orboo, or by any reortthat uses the Data Model as a data source# ith exlicit calculated "ields, created in Po%er Pivot,you can use uto/u! to auto!atically create calculated "ields using standard aggregations, or youcan create your o%n by using a "or!ula created using Data nalysis Exressions D.#

 s you can i!agine, creating calculated "ields can hel you analy'e your data in i!!ense ando%er"ul %ays, so let$s get started learning ho% to create the!#

-reating calculated "ields in Po%er Pivot is easy %hen you use AutoSum#

1# In the FactSales table, clic on the Profit colu!n#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 14/15

2# -lic Calculations 4 AutoSum# ?otice a ne% calculated "ield na!ed Sum of Profit %asauto!atically created in the cell in the -alculation rea directly beneath the Profit colu!n#

3# In Excel, in /heet1, in the "ield list, in FactSales, clic Sum of Profit#

That$s itO That is all it taes to create a calculated "ield using a standard aggregation in Po%er Pivot#

 s you can see, in =ust a coule o" !inutes, you created a /BM o" Pro"it calculated "ield and addedit to the PivotTable, !aing it easy to analy'e ro"its deending on the "ilters alied# In this case,you see /u! o" Pro"it "iltered by the Product -ategory and Dates hierarchies#

)ut, %hat i" you need to do so!e !ore detailed analysis, lie nu!ber o" sales "or a articularchannel, roduct, or category; >or that you %ill need to create another calculated "ield that countsthe nu!ber o" ro%s, one "or each sale in the >act/ales table, deending on the "ilters alied#

1# In the >act/ales table, clic on the SalesKe" colu!n#

2# In Calculations, clic the do%n arro% on AutoSum 4 Count#

3# @ena!e the ne% calculated "ield by clicing on Count of SalesKe" in the calculation areaand then in the "or!ula bar, change Count of SalesKe" to =ust Count, and then ressEnter# Bnlie calculated colu!ns, calculated "ield na!es are included as art o" the D"or!ula#

5# In Excel, in /heet1, in the "ield list, in FactSales, clic Count#

?otice a ne% colu!n, Count is added to the PivotTable sho%ing the nu!ber o" sales deending onthe "ilters alied# ust lie %ith the /u! o" Pro"it calculated "ield, you see -ount "iltered by theProduct -ategory and Dates hierarchies#

+et$s create another# This ti!e, you %ill create a calculated "ield that calculates the ercentage o"total sales "or a articular context or "ilter# Lo%ever, unlie the revious calculated "ields you created

by using uto/u!, this ti!e you %ill !anually enter a "or!ula#

1# In the >act/ales table, in the -alculation rea, clic an e!ty cell# Ti: The to le"t cell isgreat lace to start lacing your calculated "ields# It !aes the! easier to "ind# <ou can!ove around any calculated "ield in the -alculation rea#

2# In the "or!ula bar, tye and use Intelli/ense to create the "ollo%ing "or!ula: Percentage o" ll Products:H-ountJ-+-B+TE-ountJ, ++Di!Product..

3# Press E?TE@ to accet the "or!ula#

5# In Excel, in /heet1, in the "ield list, in FactSales, clic Percenta%e of All Pro$ucts#

6# In the PivotTable, !ulti&select the Percenta%e of All Pro$ucts colu!ns#

7# Fn the -ome tab, clic &umber  4 Percenta%e# Bse t%o deci!al laces to "or!at eachne% colu!n#

hat this ne% calculated "ield does is calculate the ercentage o" total sales "or a given "iltercontext# In this case, our "ilter context is still the Product -ategory and Dates hierarchies# <ou cansee "or instance, co!uters as a ercentage o" total roduct sales have increased over the years#

7/23/2019 Tutorial Power Pivot

http://slidepdf.com/reader/full/tutorial-power-pivot 15/15

-reating "or!ulas "or both calculated colu!ns and calculated "ields %ill be "airly easy "or you i" youare "a!iliar %ith creating Excel "or!ulas# hether you are "a!iliar %ith Excel "or!ulas or not, agreat lace to learn the basics o" D "or!ulas is by steing through the lessons in Kuic/tart:+earn D )asics in 30 Minutes#

/ave your %or

/ave your %orboo so that you can use it %ith other tutorials or "urther exloration#

?ext stes

 lthough you can easily i!ort data "ro! Excel, it$s o"ten "aster and !ore e""icient to i!ort usingthe Po%er Pivot add&in# <ou can " ilter the data that you$re i!orting, excluding colu!ns you %on$tneed# <ou can also choose %hether to use a Auery builder or Auery co!!and to retrieve the data#

 s a next ste, learn about these alternate aroaches: et data "ro! a data "eed in Po%er Pivot and I!ort Data "ro! nalysis /ervices or Po%er Pivot#

Po%er (ie% reorting is designed to %or %ith Data Models si!ilar to the one you =ust built# @eadon to learn !ore about the rich data visuali'ations that Po%er (ie% brings to Excel: /tart Po%er

(ie% in Excel 2013 and Po%er (ie%: Exlore, visuali'e, and resent your data#

Try enhancing your Data Model to !ae better Po%er (ie% reorts by "ollo%ing this Tutorial:Fti!i'e your Data Model "or Po%er (ie% reorting

Applies To. Excel 2013