{"id":536,"date":"2019-04-16T18:13:12","date_gmt":"2019-04-16T15:13:12","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=536"},"modified":"2019-05-06T12:47:54","modified_gmt":"2019-05-06T09:47:54","slug":"using-calculated-fields-and-items-in-excels-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/using-calculated-fields-and-items-in-excels-pivot-tables\/","title":{"rendered":"Using Calculated Fields and Items in Excel&#8217;s Pivot Tables"},"content":{"rendered":"\n<p>In this lesson we will try to explain and use a very useful feature of <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/learning-about-pivot-tables\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Pivot Tables (opens in a new tab)\">Pivot Tables<\/a><\/strong>. The creation of Calculated fields and items.<\/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\/7pVbSrvgvIo?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\/calculated-fields-and-items.xlsx\">calculated fields and items<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/calculated-fields-and-items.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>Let\u2019s start by\ncreating a pivot table. We select our data and click on pivot table. We add the\nproduct field on the columns and Salesman on the rows.<\/p>\n\n\n\n<p>We want to show income, in dollars, that each salesman,\nproduced, per product and in total.<\/p>\n\n\n\n<p>This is where we have\na small problem. To have the data we want on the pivot table we need to add a\nfield in its values, which shows the income produced per sale. Unfortunately,\nwe don\u2019t have such a field.<\/p>\n\n\n\n<p>We now face two\noptions. We either create a new column in our original table to calculate the\nincome per sale, and add it to our pivot table, or we create a calculated\nfield.<\/p>\n\n\n\n<p>You guessed right, we\nwill choose the latter.<\/p>\n\n\n\n<p>We click on our pivot\ntable and from the analysis tab of the ribbon we click on the \u201cfield, items and\nsets\u201d button. We choose Calculated field from the dropdown menu.<\/p>\n\n\n\n<p>As name for the field\nwe choose \u201cincome per sale\u201d.<\/p>\n\n\n\n<p>In the formula textbox\nwe insert the field Quantity multiplied by the field \u201cPrice per Item\u201d and then\nclick on add.<\/p>\n\n\n\n<p>A new field has just\nbeen added to our fields list. We drag it on the values area and we have the\ndesired result.<\/p>\n\n\n\n<p>We used a simple\nmultiplication as formula for our field, but we can use any Excel function as\ncomplex as we want for the calculation of the field.<\/p>\n\n\n\n<p>Having a closer look\nat our table we see that we have two products, (shoes and boots) which we would\nprefer to calculate together.<\/p>\n\n\n\n<p>The options now are to\nchange our original table and rename all boots and shoes records to \u201cboots and\nshoes\u201d, which we cannot do since their price is different.<\/p>\n\n\n\n<p>To do the required\ncalculation manually which could have some meaning in such a small dataset but\nnone in every other case.<\/p>\n\n\n\n<p>And to create a\ncalculated Item, which it seems to be the only viable choice.<\/p>\n\n\n\n<p>So a calculated item\nis a way to summarize two different sets of data items.<\/p>\n\n\n\n<p>We click on the\nproduct headers of our pivot table and from the same dropdown menu as before we\nchoose Calculated item.<\/p>\n\n\n\n<p>We clicked on the\nproduct headers because, it is for the product field, we need a new item.<\/p>\n\n\n\n<p>As name we type \u201cShoes\nand Boots\u201d and as a formula we select the Shoes item added to the boots item. <\/p>\n\n\n\n<p>In the formula field\nwe can use any excel formula as long as the items we use are part of the\nproduct field.<\/p>\n\n\n\n<p>We then click on add.<\/p>\n\n\n\n<p>A new column just\nappeared with the name of the item we just created and the needed values\ndepicted.<\/p>\n\n\n\n<p>We can even use these\nitems to filter our table and show just the income for the sale of shoes and\nboots.<\/p>\n\n\n\n<p>There is a\ndisadvantage though. If you noticed the totals column before and after the item\ninsertion you would realize that the totals have changed since the shoes and\nboots data are calculated twice. This can be corrected by filtering out the\nShoes and boots column when we want to have the real total values or by\nremoving the new item altogether.<\/p>\n\n\n\n<p>To do that we go to\nthe same dropdown menu choice as before. We select the item we want to remove.\nAnd click on delete.<\/p>\n\n\n\n<p>The totals have been\ncorrected. We can do the same to remove the new field we created as well. Click\non calculated field and then choose the new field, and click on delete.<\/p>\n\n\n\n<p>Analyzing data can be fun, right?<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this lesson we will try to explain and use a very useful feature of pivot tables. The creation of Calculated fields and items.<\/p>\n","protected":false},"author":2,"featured_media":537,"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,39,40,11,37],"class_list":["post-536","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-analysis","tag-calculated-fields","tag-calculated-items","tag-data","tag-pivot-tables"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/clacfields.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8E","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/536","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=536"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/536\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/537"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}