+ Reply to Thread
Results 1 to 7 of 7

Subtracting columns

  1. #1
    Registered User
    Join Date
    12-22-2015
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    13

    Subtracting columns

    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?
    Attached Images Attached Images

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Subtracting columns

    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)

  3. #3
    Registered User
    Join Date
    12-22-2015
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Subtracting columns

    Quote Originally Posted by XLent View Post
    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)
    Unfortunately, I'm unable to use a calculated item because the date row is a grouped field. I receive the attached error.
    Attached Images Attached Images

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Subtracting columns

    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.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Subtracting columns

    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

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Subtracting columns

    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...

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Subtracting columns

    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.
    Please Login or Register  to view this content.
    Capture.PNG
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Problem with multiple columns subtracting off each other
    By CommanderCougs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2015, 06:11 PM
  2. [SOLVED] Help with subtracting numbers in columns
    By Ellen 2Excel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-04-2013, 02:55 PM
  3. Replies: 9
    Last Post: 09-18-2012, 09:16 PM
  4. Subtracting along columns.
    By Sam1984 in forum Excel General
    Replies: 5
    Last Post: 04-21-2012, 07:40 PM
  5. Matching columns and then subtracting
    By DawginAuburn in forum Excel General
    Replies: 1
    Last Post: 01-09-2012, 06:38 PM
  6. Difference between (subtracting) 2 columns
    By Lior in forum Excel General
    Replies: 4
    Last Post: 12-09-2005, 09:45 AM
  7. subtracting columns one at a time?
    By alnorman in forum Excel General
    Replies: 0
    Last Post: 09-08-2005, 01:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1