4 Replies Latest reply on Sep 11, 2018 11:42 PM by Shashank Jain

    How to get Grand Total that is the numeric sum of the table calculation?

    Archana Amarnath

      Hi,

       

      I have a field which uses table calculation. It is in percentage format. When I show the grand total, the values are not the numeric sum of the percentages.

       

      Attaching a sample of the dashboard. In this the field "Price new" uses table calculation.

      As an example : So for column 200001 I need the grand total to show the value as 9.62% (4.61%+0.37%+4.64%).

       

      Is there a way of getting this?

      Also is there a way to change this sheet into a Stacked Bar Chart?

       

      Thanks,

      Archana

        • 1. Re: How to get Grand Total that is the numeric sum of the table calculation?
          Nathan Krisanski

          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.

          • 2. Re: How to get Grand Total that is the numeric sum of the table calculation?
            Joe Mako

            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.

            • 3. Re: How to get Grand Total that is the numeric sum of the table calculation?
              Joe Mako

              Nathan,

               

              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.

              • 4. Re: How to get Grand Total that is the numeric sum of the table calculation?
                Shashank Jain

                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

                as below.

                 

                RUNNING_SUM(SUM([TX Qty]))*AVG([Close])

                 

                // 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.