Calculated fields unavailable as pivot table built from data model, so how do I add calculated Surplus / Deficit to Income and Expense Statement as a line at the bottom?
The number is automatically calculated in the data model as an implicit measure but not sure how to add it to report.
Thanks
Indeed, you cannot add calculated items to a Power Pivot table. If it's just the bottom line you want labelled "Surplus/Deficit" why not just replace the words "Grand Total" with "Surplus/Deficit" on the grand total row?
Though, you could use Power Query to add some rows to the Income and Expense data for a Category Type "Surplus/Deficit". Load to the DM and then it will show up automatically. Just collaps the Surplus/Deficit group as you don't want to see all the category details, I suppose.
Both ways are included in the attached file. By the way, it uses a data set from Mynda's Ultimate Personal Budget template.
@ stockjam Edit: Just noticed I made a mistake when I grouped the rows for Budget Surplus/Deficit. Revised file attached.
Hi Riny
I didn't do the first option as income and expense values are both positives, so Grand total adds them together.
I'll try option 2 and see how I go. There are account, month and job codes coluns in the PQ and Model data, for reporting display and filtering as well.
Will reply with how I go
Thanks
@stockjam Just thought of a more effective solution. Similar to the one mentioned above, but with more condensed grouping after realising that you don't need to include the Catergory.
