Chapter 17 · 17.7
Margin as a percentage in a pivot table
You have a pivot table showing the turnover and cost price per product, and you want the margin as a percentage next to it. In Excel you would create a "calculated field" for this. You won't find that button in Euro-Office: calculated fields don't exist here. Adding a "margin %" column to the list and summing it in the pivot table gives nonsense, because you can't add up percentages. There are two good ways to do it.
As an example, we use Bakkerij De Korenaar, with the daily turnover and cost price of Bread, Pastry and Pie.
Step 1: the pivot table
Create a pivot table from the list (see Pivot tables and A pivot table by month, quarter or week) with Product under Rows and Turnover and Cost price under Values, as a sum. The pivot table starts in A3 on the new worksheet, with the headings Row Labels, Sum of Turnover and Sum of Cost price in row 4 and the products from row 5 onwards.
Method 1: the margin next to the pivot table
If the sum of the turnover is in column B and the sum of the cost price is in column C, the margin of the first product in row 5 is simply =(B5-C5)/B5. Give the cell the Percentage format and drag the formula down to the last product row.
This works as long as the pivot table doesn't change. If a product is added, or you sort differently, the rows shift and your formula looks at the wrong product. To prevent this, use GETPIVOTDATA. It looks up the number by name, wherever it is:
=(GETPIVOTDATA("Sum of Turnover",$A$3,"Product",A5)-GETPIVOTDATA("Sum of Cost price",$A$3,"Product",A5))/GETPIVOTDATA("Sum of Turnover",$A$3,"Product",A5)
- The first argument is the name of the value field, exactly as it appears in the heading of the pivot table: Sum of Turnover, not just Turnover. With Turnover alone you get #REF!.
- The second is a cell in the pivot table, here the top-left corner A3.
- After that come pairs of field and item: the field Product and the product name in A5. You can also type the name as text: =GETPIVOTDATA("Sum of Turnover",$A$3,"Product","Pie").

Method 2: the profit in the list
Add a Profit column to the list itself, with Turnover minus Cost price on each row. Profit is something you can add up. In the pivot table, put Turnover and Profit under Values and calculate the margin next to it as profit divided by turnover. This is often the clearest option.
What Euro-Office does have
- Show values as: right-click an amount in the pivot table. With % of grand total you see what share of the turnover each product represents. That isn't a margin, but it's often what people mean by "the percentage per product".
- Calculated Items on the Pivot Table tab: these let you calculate within a single field, for example an extra row "Bread and pastry" that adds the two together. You can't use them to calculate between two value fields, such as turnover and cost price.
Pitfalls
- Adding up percentages in the list: a margin % column in the source gives a wrong total in the pivot table, whether as a sum or an average, because large and small rows count equally.
- #REF! with GETPIVOTDATA: the field name is spelled differently (for example Turnover instead of Sum of Turnover), or the product is no longer visible in the pivot table. Check the heading of the pivot table to see exactly what the field is called.
- A calculated field from Excel can't be created in Euro-Office. If a file with such a field also needs to keep working in Excel, edit the calculated field in Excel.
