+ Reply to Thread
Results 1 to 11 of 11

adding a line to my graph

  1. #1
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    adding a line to my graph

    Hello,

    is it possible to add a cumulative line to my graph or do I have to make an extra table for it?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: adding a line to my graph

    Yes, it is possible to add a cumulative line to your chart. Charts don't really do any analysis, so you will need to calculate your cumulative totals somewhere in the spreadsheet. Steps I would expect:

    1) Compute cumulative totals somewhere in the spreadsheet. It's not clear to me how you intend to calculate these cumulative totals, so I cannot make a specific recommendation. Easiest place in the spreadsheet is probably in column E of draaitabel.
    2) Add column E (or the column you used for the cumulative totals) to the chart as a new data series (Select Data dialog).
    3) Select the cumulative total data series and change the chart type from column to line.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: adding a line to my graph

    I cannot just use select data dialog since it is based on a pivot table than it is blocked.

    I made a new table with the cumulative revenue but I cannot get in my graph since I have to make relations or something. How does that work?
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: adding a line to my graph

    I did not understand from the OP that this was a pivot chart. That is different. One of the limitations of a pivot chart is that it cannot use any data from outside of the pivot table. Basically, two options:

    1) Figure out how to get your pivot table to compute the cumulative data for you.
    2) Don't use a pivot chart (which can be tricky). See this tutorial discussing the pros and cons of pivot charts and how to build a regular chart using pivot and non-pivot data: https://peltiertech.com/regular-char...-pivot-tables/

  5. #5
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: adding a line to my graph

    But I did choose a different pivot table in which you can combine various tables into 1 chart but I dont know how to establish the relations

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: adding a line to my graph

    I don't know my way around pivot tables and charts, so I cannot comment on what you have done or how to get it to do what you are asking. Let me see if someone more familiar with Excel's pivot table/chart tools will come in and help.

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

    Re: adding a line to my graph

    As you are using the 2016 version of Excel, you could use Get & Transform to convert the data from cross tabulated to row over row and then add a running total column.
    The advanced editor code for the conversion is:
    Please Login or Register  to view this content.
    The formula for the running total column is: =SUM(Q1,P2)
    On the Pivot chart sheet the pivot table has Attribute in the Rows area and Value and Running Total in the Values area.
    The pivot chart displays Value using columns and Running Total as a line which is plotted on the secondary (right hand side) axis.
    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.

  8. #8
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: adding a line to my graph

    thank you that is a start I guess. Only in your example sum of running total is my cumulative line but the total of running total is more than 3 billion --> € 3.334.245.680 while the total cumulative can not be more than the total of my data which is € 2.310.000 right?

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

    Re: adding a line to my graph

    Sorry about that.
    In the pivot table change the field value setting of the Running Total field from Sum to Max.
    Let us know if you have any questions.

  10. #10
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: adding a line to my graph

    thank you well I have to try this weekend at home with my original data because when I try to do it I get the same set up as in my original data but with cumulative as a row on the bottom not as a column on the right like you mentioned and by just copying your code I dont know how to do it

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

    Re: adding a line to my graph

    I am unsure as to what "I don't know how to do it" means.
    If you mean that you don't know where to put the advanced editor code then:
    1. Open the file from post #1
    2. Select one of the cells in the table on the € sheet
    3. Select the Data tab and then select From Table/Range
    4. When the Power Query Editor opens select Advanced Editor
    5. Copy the code from post #7 and use it to replace the pre existing code
    6. Select Close and Load To and when the dialog box appears select OK
    If that isn't the reason for the "I don't know how to do it" then please give us some details.

+ 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. Adding multiple data series on bar graph with line graph on top
    By JPRICH16 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-27-2019, 04:43 AM
  2. Adding a line to a bar graph
    By Cmorgan in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-10-2014, 10:22 AM
  3. Difficulty adding a trend line to line graph in Excel 2007
    By jmatoske in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-28-2012, 02:59 PM
  4. Replies: 2
    Last Post: 05-16-2010, 11:23 PM
  5. Adding a lower limit line to line graph
    By jsmity in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-31-2009, 05:31 AM
  6. Adding a line to graph
    By b50 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-29-2009, 05:09 AM
  7. Adding a line on graph
    By Dirk_Bob in forum Excel General
    Replies: 0
    Last Post: 12-14-2005, 03:25 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