What is a Calculated Field?
How to create a Calculated Field?
Step 6: Now click on Home | PivotTable | PivotTable and select New Workbook and click on Ok to run the PivotTable in a new Excel Window.
Calculated field is a custom field that calculates each cell value in PowerPivot window. To create a report sometimes you may need to add a custom column for a table for getting a result. For example, in my previous article, I used a tblOrdersData table where there is no column for Amount. But in a Sales Report it is essential to know the amount against an invoice.
You might think, then tblOrdersData table needs to modify and add a column named Amount. But the answer is No. As we had previously created Relationship among tables. We can easily create this calculated column in tblOrdersData table through Relationship. So question is how to do it? And the solution is here.
How to create a Calculated Field?
Step 1: We have seen that in tblOrdersData table has a column Quantity but there is no column named Amount. For Sales Report you need to add it. To do this first of all you need to create a relationship in between "tblProducts" and "tblOrdersData" where "tblProducts" is used for Primary Key and "tblOrdersData" will used for Foreign Key like below image:
Step 2: After creating the relationship in between these 2 tables, the Diagram View would looks like below:
Image 1: Relationship in Diagram View
Step 3: Return to Data View mode by clicking on Home | Data View in PowerPivot window. And click on tblOrdersData tab to activate the tblOrdersData tab in PowerPivot window. Now click on Design | Add button from Columns group.
Step 4: After clicking on Add button, the formula bar in PowerPivot will activate and your cursor will stay in "Add Column" column anywhere. Now type the following formula to calculate the amount against each product in each invoice like below:
=RELATED(tblProducts[Unit_Price])*[Quantity]
Image 2: tblProducts related tables unit_price column will multiply in Quantity column
Step 5: You are almost done, just need to rename the CalculatedColumn1 to Order_Amount by double clicking on CalculatedColumn1 column header and type Invoice_Amount.
Image 3: PivotTable field settings
And here is the final report with amount:
Image 4: Retail vs Product wise Invoice amount