Hello,
In the attached screenshot, considering the way the data is structured, what is the best way to subtract the debit column from the credit column for each row?
Hello,
In the attached screenshot, considering the way the data is structured, what is the best way to subtract the debit column from the credit column for each row?
If you have 'credit' selected as active cell, on the Pivot, you should find you can add a Calculated Item (via Analyze->Fields/Items/Sets on Ribbon)
in resulting dialog, enter appropriate heading (e.g. Net), select Transaction Type in dialog, at which point you shou;d see Debit & Credit on right hand side, then enter the formula of =Debit-Credit
new item should then appear on the Pivot (and you can remove Grand Total)
Ah.... whilst the error dialog implies you can ungroup, add item, and then re-group - it won't work.
As far as I'm aware the only way you could get the same effect would be to "group without grouping" by adding a field at source to conform the dates such that resulting values mimic grouping, at which point you could just as easily / alternatively create a Net column [i.e. Amount * 1/-1 pending transaction type]
(the Net route would be the better one IMO)
Above said, I'll post a link to this thread elsewhere as someone may know of a workaround.
That is the usual workaround. (either that or add additional columns for the debit and credit so that you can use a calculated field rather than calculated item; the latter being very limiting and slow in my experience.)
Rory
If you load your data to the Data Model, and create measures, you can solve this.
Attach a sample workbook, so I can create a meaningful solution.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Actually, that's simple data to recreate. See attached workbook - I just added the source data table to the data model, then added three simple measures:
Please Login or Register to view this content.
Please Login or Register to view this content.
Capture.PNGPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks