You cannot add calculated item to grouped filed.” You may get error message like above saying “This PivotTable report field is grouped.
So make sure that you have selected Data Source filed before getting calculated item option In case you forget to select Data Source filed on PivotTable, calculated item will remain disabled. Select Data Source filed then Click PivotTable Tools > Formulas > Calculated Item Hide the GrandTotal column by right click on GrandTotal > Click Remove GrandTotal Step 3 – Add calculated item You can drag and show Budget column before the Actual. Step 2 – Separate Budget and Actual by adding Data Source filed to the column area Now your P&L report will show single figure which included budget and actual both. We will convert this basic P&L report into Budget V/s Actual Comparison with following steps Step 1 – Change data source / increase data rangeĬlick PivotTable Tools > Change Data Source
#Subtotal versus total versus grandtotal download
You can download new file with updated data and basic Pivot P&Lįile : Updated Data with Budget.xls Existing data is marked as Actual and I have added more data rows which are marked as Budget. I have added column Data Source to the end of data table. You can also do budget v/s actual comparison using Pivot Tables.įor this we have to add one more column to our data. This is continuation of our earlier post Preparing Quarterly and Half yearly P&L using grouping option. Quarterly and Half yearly Profit Loss Reports in Excelīudget V/s Actual Profit Loss Report using Pivot Tables Preparing Pivot Table P&L using Data sheetĪdding Calculated Fields to Pivot Table P&L This is last part of Profit & Loss Reporting using Excel series, written by Yogeshĭata sheet structure for Preparing P&L using Pivot Tables
0 kommentar(er)
