Hello,
is it possible to add a cumulative line to my graph or do I have to make an extra table for it?
Hello,
is it possible to add a cumulative line to my graph or do I have to make an extra table for it?
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.
Originally Posted by shg
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?
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/
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
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.
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:The formula for the running total column is: =SUM(Q1,P2)Please Login or Register to view this content.
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.
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?
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.
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
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks