I don't think what you are trying to do is possible. The problem comes about because of needing to use a windowed function (total(sum()) to get the Base Month Price final. The grand total is actually showing you the percentage for all your areas combined, not the sum of the percentages...
Without some knowledge of exactly what you are trying to do, its hard to suggest a fix, but when I come against issues like this I normally have to resort to updating my source data first so that I don't have to use a windowed function to get the total. Ie. append another column to your dataset that is the Base Month Price Final. Then your function is just a base sum, and this should add when you get to the grand total.
Not sure if that helps, but I hope you get the idea.
In your example situation, you can use a nested table calc, and a dup of your dimension you want the Grand Total on, to get the result you are looking for.
This route is very flexible in a wide variety of situations. See the attached for an example.
You can learn more about Grand Total in the 3-part posts at http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/
Nathan, Archana, or anyone else, you are welcome to reach out to me if you would like more details.
Notice that TOTAL is an aggregation at a different level, and would not work in this case, while the WINDOW_ calcs are an aggregation of an aggregation, and the dimension pills on the Marks card impact the Grand Total.
Sample 3 jm edit.twbx.zip 158.0 KB
You said "when I come against issues like this I normally have to resort to updating my source data first so that I don't have to use a windowed function to get the total."
Please get in touch with me if you would you like to learn a perspective on table calcs so this is not longer the case.
Hello Joe, somehow attached work book is not opening in my 64 bit 2018.2 version. Am I missing something here ?
I too am grappling with a problem of getting the totals of a Calculated field, Current value, which is using table calculation
// Tx Qty is no of shares purchased on a particular date
// close is daily close price
Basically I have historical price of stock share ( Synmol ) close price, via ggoglesheets which automatically updates on daily basis. Say I have 10 companies in my portfolio, which I union to get historical price for last 5 years for each date. Then I have excel file for recording transactions done ( Buy/Sell ) having fields as Symbol, Tx date, Tx quantity ( + for buy and - for sell ) and the the price of transaction . I then join this Transaction table to the Union of all 10 symbols.
I want to now analyse the investments done and the current value of each Symbol on a daily time series.
Getting individual line graphs is easy, but if I have to aggregate for all the the 10 symbols it does not work.
Any pointers would be helpful. Please let me know so I will send the .twbx file along with sample data sheets..
Further if it works , I would like to do all sorts of other analysis like percent gain / loss, CAGR , etc on individual Symbols, as well as on aggregate.