Log In
Register
Home
June 22, 2019
Offline
Hi,
Unfortunately I'm not into DAX - I work in Excel with Pivot, and some Power Query and Power Pivot as well.
I would really like some help to make a running total graph, consisting of 2 lines: Red for amount (money used), and blue for budget - both aggregated, for a given period of time (f.x 201904 is April, 201905 is May etc).
Since I'm not into DAX, I've tried via Power Pivot importing some dimensions to Excel Pivot: Periode, Amount and Budget, and changing calculation via Excel menu to running totals for Amount and Budget.
However for periods where Amount is 0 (May-December in my example), it seems I need Amount to be empty (nothing), and not the number 0. As long it's null the red line will draw a wrong visual result for May to December. However the blue line is just OK, showing 12 monhts accumulated budget all the time.
I'll illustrate with a figure what I want to achieve. Monts in figure is January to December (in my language Januar to Desember), Regnskap is Amount, Budsjett is Budget. Because this graph is made not from a Pivot table, it was easy for me to delete Amount for May to December, and get a nice red line ending in April. However in Pivot it's not easily possible to delete numbers like that for Amount.
Also I'm using Power Pivot with filters to update the table for my wanted graph (choosing periods, costplace, project etc.), getting updated numbers for just that selection. A flexible solution when choosing Period from filters, should also adjust the Periods in the graph to automatically empty itself (Amount) for periods not choosen =)
I think a solution here could help a lot of people out there, it's such a wonderful graf this one. I just call it 'the golden graph'.
Thanks in advance for any help making this a solution.
With regards,
Trond in Norway
VIP
Trusted Members
December 7, 2016
Offline
Hello neighbour,
Have you tried with replacing the 0 values in your data with NA()? The graph will ignore cells with the #N/A error.
If you need more hands on help then I advise you to upload a sample file for us to check and work with.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Trond,
Build another measure to display BLANK() instead of zero:
=IF(SUM(tblTransactions[Amount])=0,BLANK(),SUM(tblTransactions[Amount]))
June 22, 2019
Offline
Hi Anders (neighboor:)
See attached file. From 201906 (June) to 201912 (December) there should be no values in the pivot table showing running totals.
But seems impossible to just delete those values in a pivot table, making 201906-12 blanks. Therefore the red graph draws up strangely.
Trond
June 22, 2019
Offline
Hi Catalin!
Unfortunately I'm not into measures formulas. If you see the attachment I just uploaded, how to insert/use measurement in this example?
Also, I'm using slicers for Period, and it must be easy to update period 201906, 201907 etc. with new running totals, as the months go by..
If possible connecting a measure to Period slicer for just the Amount column could be a very nice and flexible solution - with Budget showing all 12 months all the time.
I've attached the file with slicers for Period as well here.
Trond
July 16, 2010
Offline
Hi Trond,
I'm afraid you'll have to embrace DAX for the best solution, anything else would be manual. Here is a step by step tutorial explaining what is required.
Mynda
June 22, 2019
Offline
Hi Mynda,
I'm happy for any help and suggestion with 'the golden graph'. It seems like some people have been reading this thread with interest as well, but few solutions so far.
You're referring to DAX as a possible solution, but DAX is kind of complicated I think, as I've no experience with either. This shouldn't be that difficult..
What about making a workaround by using "MDX measures" instead in Excel? I think if possible to filter just one of the columns in the pivot table with running total output (the Amount column) with an MDX measure, and only for the selected monhts (connected slicers) in the Period box?
- See attachment, Amount is the column to make a MDX measure for
This could be an easy, good and flexible solution too, with Budget column still showing all 12 months, as MDX measure not connected to filtering Budget.
Hopefully running totals for Amount for unselected periods will not show up with this method in the graph.
But is it possible to use MDX this way, just connecting the selected slicers to the Amount column? =)
Trond
October 5, 2010
Offline
Hi Trond,
There's no attachment.
Regards
Phil
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Trond,
I doubt that MDX is easier than DAX. I think they are equal in complexity, it simply depends on user specific training and abilities. An SQL guy will be very comfortable with MDX, an excel user will be more comfortable with DAX because it's using formulas similar to excel formulas, many DAX functions can be found in excel functions too.
You can use Multidimensional Expressions (MDX) to query multidimensional data or to create MDX expressions for use within a cube, but first you should understand SQL Server Analysis Services dimension concepts and terminology.
Your data is far from a multidimensional data cube.
The DAX formula is fairly simple:
RunningTotalBudget:=CALCULATE (
SUM ( Tabell1[Budget] ),
FILTER (
ALLSELECTED ( Tabell1),
Tabell1[Period]<= MAX ( Tabell1[Period])
)
)
Because it's using ALLSELECTED, will work with slicer selections. If you replace ALLSELECTED with ALL, it will ignore slicer selections.
June 22, 2019
Offline
Philip Treacy said
Hi Trond,There's no attachment.
Regards
Phil
June 22, 2019
Offline
Hi Catalin,
Thanks for your reply, the code looks interesting. However it doesn't work as planned. See attachment (v4).
ALL vs ALLSELECTED: I tried replacement in code, but Budget doesn't show up for all 12 months, so ALL doesn't seem to ignore slicer selections after all.
Running totals AMOUNT: There should be no flat graph from 201906-12, running totals is just repeating itself then. Here period amount itself is zero, and running totals should be too.
Running totals BUDGET: Should be for 12 months always, no filtering with slicer selections.
Also I just need see only 2 graph lines; Amount accumulated and Budget accumulated. But now I see 4 graphical lines at same time (see attachment).
Hopefully there is some solution to my case after all.
June 22, 2019
Offline
Catalin,
It seems like attachment disappears when editing posts here?
Anyway, Excel v4 attached here =)
Trond
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Trond Liavik said
Running totals AMOUNT: There should be no flat graph from 201906-12, running totals is just repeating itself then. Here period amount itself is zero, and running totals should be too.
I think you're twisting the meaning of a running total. The calculation is correct, you want to show 0 but that's the value of the current month, not a running total.
A running total is a sum of current month values added to all previous month values. If current months values are 0, the current month running total is still the sum of all previous months, cannot be 0.
If you want it to be 0, that's no longer a running total column, i's a hybrid between a running total and the current month value, which in my opinion is wrong, will bring confusion to chart reader.
Trond Liavik said
- See attachment, Amount is the column to make a MDX measure for
But is it possible to use MDX this way, just connecting the selected slicers to the Amount column? =)
The image you uploaded is not a MDX measure, that's a Named Set created using MDX. MDX Measures are in OLAP Tools.
In the attached file, you will see all 12 months displayed, but the values will be displayed only for selected months.
You should create 2 pivot charts, one for budget and one for Amount, connect to the slicer only the Amount pivot. (the charts should be overlapped)
June 22, 2019
Offline
Hi Catalin,
I'm not trying to twist Excel out of its logic - let me therefore correct my writing, that running totals for Amount for 201906-12 should be Blank - not Zero. I agree with you that running total cannot be zero, when numbers each monts are others than zero. Blank however is another thing, I would say. Blanks in this case would show running totals Amount graph 'invisible' for 201906-12.
Also thanks for telling me about Named Set, I didn't know this with regards to MDX Measures (OLAP).
The idea of creating 2 pivot Charts is interesting, but do you know if possible to 'merge' 2 Charts into each other (like 'fluently' overlapping), in order to see both the graph for 12 months of Budget and in this case just 5 months for Amount? (with 7 months being invisible then)
I will download the attachment later today - I'm not able to right now (firewalls).
Thanks for suggestions and comments so far.
Trond
June 22, 2019
Offline
Solution found;
Formulas used:
1. TotalAmount2:=IF(ISBLANK(SUM ( Tabell1[Amount] ));"";SUM ( Tabell1[Amount] ))
2. TotalBudget2:=IF(ISBLANK(SUM ( Tabell1[Budget] ));0;SUM ( Tabell1[Budget] ))
I twisted one of your formulas slightly, and unconnected the slicer Period, and got the wanted result.
Thanx for good help Catalin :=)
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Nice, glad to hear you managed to find your way through, good job !
June 22, 2019
Offline
Hi again,
Sorry to say, but another problem came up when implementing the working solution today with my 100 thousands of rows with real data.
This fails in Excel 2010 production: TotalAmount2:=IF(ISBLANK(SUM ( Tabell1[Amount] ));"";SUM ( Tabell1[Amount] ))
The parameters ;""; obvoiusly doesn't like to cooperate in the Amount measure.
I've also tried changing "" to BLANK(), but running totals doesn't seem to work with blanks either.
The difference might have to do with Excel Power Pivot versions, as I'm using 2010 in production, and newer Excel 365 in my development environment.
Uploading a mp4-fil (video) here showing this actually works just fine in Excel 365, and the original Excel file for testing yourself (v5).
I'll keep on working with it, searching a way around. Still using DAX formulas I see could be useful here.
Trond
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
You should update first the power pivot add-in. For this, the only way is to download again the add-in and install it.
1 Guest(s)
