Custom column in a pivot table from the data model in Excel-CodePudding

I created the following table in Excel and added it to the data model:

I know that I can't insert a calculated field to a pivot table with this data as it is now included to the data model.

How could I go about creating a custom column named 'outstanding principal', which would be the sum of 'new loans' and 'paid principal' for each lender?

Desired result:

I understand how to do this using formulas included to a new column in the original table. My question regards how to do this using DAX.

CodePudding user response：

You can't have this as a calculated or custom column, since you're aggregating vertically. (Your sample data is too small to make this obvious. E.g. you don't have any aggregations in the pivot table, which is rather atypical.) So you need to go to the Power Pivot Window and enter the following measure:

outstanding principal:=
CALCULATE(
    SUM(Table[Value]);
    Table[Event] = "New Loan") 
  
CALCULATE(
    SUM(Table[Value]);
    Table[Event] = "Paid Principal" )

After that you'll see outstanding principal in your PivotTable Fields. Note that it doesn't make sense to add this measure to your existing PivotTable, since you are already filtering by Event. Put it into a new table like this: