+ Reply to Thread
Results 1 to 5 of 5

Difference Column in a Pivot Table

  1. #1
    Registered User
    Join Date
    10-16-2018
    Location
    Kentucky
    MS-Off Ver
    Office 365
    Posts
    7

    Difference Column in a Pivot Table

    I am trying to add a difference column to my pivot table and I am unsure of the best approach. My data is in a data model. I have multiple measures in the pivot table and I have the "Values" in the Rows of my Pivot Table. in the column I have the year, which is a column in my data model. So I have a two column pivot table where the rows and values are different measures and I would like to add a 3rd column that shows the Difference between the two columns.

    What is the best approach for this? Below is a simple example that shows my pivot table and how I have the rows and column setup...
    Thanks.

    image001-3.png

    image001-2.png

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Difference Column in a Pivot Table

    While it easier to help if we have a file with which to work (see instructions in the banner at the top of the page) along with manually calculated outcomes (with which to compare the results of our proposals), I would say based on the screenshot that a calculated item would be suitable for producing the difference between fiscal year 2019 and 2020.
    The link below is a tutorial on setting up a calculated item.
    https://www.contextures.com/excelpiv...ulateditem.htm
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    10-16-2018
    Location
    Kentucky
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Difference Column in a Pivot Table

    Thanks for your reply. And sorry, I should have added an example. Attached is a very simple example of what I am trying to do. My issue is I have multiple measures that are each in their own column and I would like to show them in rows as I am showing in the attached. My question is - is there a way to add a difference (2020 - 2019) measure to this pivot table that will display where I have highlighted in yellow.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Difference Column in a Pivot Table

    I can only come up with an inelegant work around.
    Three measures are added to the data model i.e.: Diff rev:=CALCULATE(SUM(Table1[Revenue]),Table1[Fiscal Year]=2020)-CALCULATE(SUM(Table1[Revenue]),Table1[Fiscal Year]=2019)
    A separate pivot table is then produced in columns D:E using the three measures.
    Column D is hidden so that it somewhat appears that column E is part of the pivot table containing columns A:C
    I also included a regular pivot table produced directly from Table1. Note that with a regular pivot table a calculated item populates the Diff. column (='2020' -'2019')
    Sorry not to be of more help.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Difference Column in a Pivot Table

    Hello,
    In my opinion, it is enough to slightly transform your table with Power Query. In another data layout, you can add a calculated field that shows the difference year over year.
    Regards
    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. Calculating difference between 2 column in Pivot Table
    By dwhite30518 in forum Excel General
    Replies: 1
    Last Post: 11-15-2018, 03:35 PM
  2. Replies: 1
    Last Post: 10-12-2018, 11:36 AM
  3. Replies: 3
    Last Post: 07-11-2018, 05:33 AM
  4. Calculating ppts difference from % of column total data in a pivot table
    By TJGriffiths in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-10-2017, 09:49 PM
  5. Pivot Table with % difference from
    By moty.98 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-14-2016, 04:55 AM
  6. Replies: 3
    Last Post: 03-17-2010, 07:28 AM
  7. Replies: 7
    Last Post: 01-30-2005, 02:06 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