Step 10: Click on ADD and OK to complete the formula. Step 9: Name this Calculated Field as Profit Percentage. Step 8: Type divider symbol (/) and insert Gross Sales Field. Step 7: Now, we must see the newly inserted calculated field Total Profit in the Fields list. Go to Analyze and again select Calculated Field under Fields, Items, & Sets. The formula to calculate the Profit Percentage is Total Profit / Gross Sales. Step 6: Now, we need to calculate the profit percentage. If I want to see the analysis country-wise and product-wise, I just have to drag and drop the product column to the ROW field it will show the breakup of profit for each product under each country. This calculated field is flexible, it is not only limited to Country-wise analysis, but we can use this for all kinds of analysis. Step 5: Now, we have our TOTAL PROFIT Column in the pivot table.
Step 4: Click on ADD and OK to complete the formula. Now type minus symbol ( – ) and select COGS > Double click. Go inside the formula bar > Select Gross Sales from the below Field and double click it will appear in the Formula bar. The formula to find the Profit is Gross Sales – COGS. Step 3: In the Formula section, apply the formula to find the Profit. Step 2: In the below dialog box, give a name to your new calculated field. Go to Analyze tab in the ribbon and select Fields, Items, & Sets. Step 1: Select a cell in the pivot table. We can add these two columns to the pivot table itself. I need to find out the profit and profit percentage for each country. The problem is I don’t have a profit column in the source data.
Below is the pivot table for the above data. Let me apply the pivot table to find the total sales and total cost for each country. Please take a look at the below data I have Country Name, Product Name, Units Sold, Unit Price, Gross Sales, COGS (Cost of Goods Sold), Date, and Year column.
This is the most often used calculated field in the pivot table.
Microsoft excel pivot tables download#
You can download this Pivot Table Formula Excel Template here – Pivot Table Formula Excel Template
0 kommentar(er)
