{"id":553,"date":"2019-04-17T10:43:32","date_gmt":"2019-04-17T07:43:32","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=553"},"modified":"2019-05-06T12:50:29","modified_gmt":"2019-05-06T09:50:29","slug":"hierarchies-with-powerpivot-in-excel","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/hierarchies-with-powerpivot-in-excel\/","title":{"rendered":"Hierarchies with PowerPivot in Excel"},"content":{"rendered":"\n<p>Let us take the next step in power pivot tables. We will talk about hierarchies in <a href=\"https:\/\/www.test4u.eu\/excelpedia\/using-the-powerpivot-add-in-with-excel\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Power Pivot (opens in a new tab)\">Power Pivot<\/a> and will show a bit more of its functions.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/NZ9Z0pGldbw?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/power-pivot-hierarchy.xlsx\">power pivot hierarchy<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/power-pivot-hierarchy.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>In our current sheet\nwe have two tables already added in the data model. So we click on Manage on\nPowerpivot tab.<\/p>\n\n\n\n<p>We can see the two\ntables. We toggle the diagram view, and notice the relationship between them.<\/p>\n\n\n\n<p>Before we talk about\nhierarchy, let\u2019s click on the design tab and create a date table.<\/p>\n\n\n\n<p>A new table has been\ncreated. This is basically a table of all the dates in the year.<\/p>\n\n\n\n<p>We will create a\nrelationship between our date field on the first table and the date field on\nthe new table.<\/p>\n\n\n\n<p>If you notice on the\nCalendar table, there is a field called date Hierarchy.<\/p>\n\n\n\n<p>The hierarchy field is\na collection of columns that we can create as child levels nested under the\nfirst in their order.<\/p>\n\n\n\n<p>For example, the\nhierarchy field will show us the Year and nested under it as child levels of a\ntree-like form the month and the date column.<\/p>\n\n\n\n<p>Let\u2019s create a\nhierarchy of our own in the second table. We right click on the Product field\nand select create Hierarchy.<\/p>\n\n\n\n<p>We will use the name\n\u201cProduct and Price\u201d.<\/p>\n\n\n\n<p>We need to add the\nprice per item field in it. We can either right click on the field and add it\nto the hierarchy, or we can simply drag it and drop it in the required order.<\/p>\n\n\n\n<p>If we right click on a\nhierarchy field, we have a number of choices. We can move it up or down, which\ncan be accomplished with drag and drop as well. <\/p>\n\n\n\n<p>We can also rename it,\nwhich we will do on the DateColumn field, and will set it to \u201cdate of sale\u201d.<\/p>\n\n\n\n<p>We will hide the\noriginal column name from all hierarchy fields, to avoid mixing them up with\nother similarly named fields in our model.<\/p>\n\n\n\n<p>Now it\u2019s time to see\nhow these all affect our pivot tables. We create a new pivot table on a new\nsheet to have more room.<\/p>\n\n\n\n<p>We set the date\nhierarchy as column, and Products and price as row. The sum of quantity is what\nwe need to see.<\/p>\n\n\n\n<p>We can see that in the\ncolumn section only one column exists. The year 2016. This is the first field\nin the date hierarchy. By clicking on the plus sign we can see that a lot more\ncolumns appeared showing the months that at least one sale took place. This is\nthe second field of our hierarchy.<\/p>\n\n\n\n<p>So if we click on the\nplus sign of February we expect to see the third field (date of sale) appear\nunder it. <\/p>\n\n\n\n<p>It is very convenient\nto have a flexible way to review our data. In this example we can see the dates\nof sale from February only without having to see all the columns of all the\ndates of sales. Something like this could make our data unreadable.<\/p>\n\n\n\n<p>In the rows hierarchy\nfield, the nested field provides us with just some extra information about the\nparent field which is the price in this example.<\/p>\n\n\n\n<p>Reviewing our table,\nwe decide that the date of sale field is useless for our current analysis. So\nwe go back to powerpivot and Remove it. We save our changes and review the new\npivot table.<\/p>\n\n\n\n<p>We notice that since\nthe sales are all for one year we really do not need the year subtotal. We\nright click on it and disable it.<\/p>\n\n\n\n<p>This is the final layout of our pivot table. <\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Let us take the next step in power pivot tables. We will talk about hierarchies in Power Pivot and will show a bit more of its functions.<\/p>\n","protected":false},"author":2,"featured_media":555,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[38,11,45,46,37,44],"class_list":["post-553","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-analysis","tag-data","tag-data-analysis","tag-hierarchy","tag-pivot-tables","tag-powerpivot"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/hierarchies.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8V","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/553","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=553"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/553\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/555"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}