1 Reply Latest reply on Aug 9, 2016 4:59 PM by Stoyko Kostov

    Grand Total Order of Operation

    Kaylan Hazlett

      I have a workbook that is using data from two different sources, one has Transactions, the other has Per Transaction Goal ($). I have created a calculated field that shows Transactions*Per Transaction Goal. The calculated field requires me to SUM them since they are coming from two different sources, but there is really only one line of data for each in each source.

       

      The calculated field looks fantastic, until I add the grand total. In that case, it is summing the Per Transaction Goal (this is the issue) and multiplying that by the sum of Transactions. I have tried some of the fixes I have seen in the forums, but none of them that I can find seem to help this particular issue. It seems like the two data sources is what is causing me grief.

       

      See screenshot attached, the Grand Total should be closer to $205K in this case, not $1.3M.

       

      Thanks!

        • 1. Re: Grand Total Order of Operation
          Stoyko Kostov

          Hi Kaylan,

           

          When working with multiple data sources, the aggregating function you need to use in order to compute totals is WINDOW_SUM(). TOTAL() would not work on calculated fields that reference multiple data sources.

           

          I came up with a hacky solution to your problem. I compute the grand total in a separate sheet, and then combine the 2 sheets in a dashboard.

           

          First, I define the following calculated field, and name it GrandTotal:

           

          IIF(LAST()=0,WINDOW_SUM([PerTran].[TranTotal]),null)

           

          I need the IIF so this only shows for the last store. If you just use WINDOW_SUM([PerTran].[TranTotal]), it will show up for all stores.

           

          Create a second sheet, and drag Store to Rows and GrandTotal to Text. Select all stores except the last one, right-click and choose Hide. Now right-click on the last remaining store, and deselect Show Header. You will only get one number - the total - displayed.

           

          Now you can combine the 2 sheets in a dashboard: choose floating layout, drag sheet 1 onto the dashboard, and then sheet 2 immediately below. You will need to play with the layout to get it precisely positioned. On Sheet 2, you can also right-click and deselect Title so that it appears as a single table in your dashboard.

           

          Note that I did not sum the PerTran and Transaction columns, as it doesn't make sense. You only want to sum the product of the two.

           

          Check the workbook I'm attaching, and let me know if you have any questions.

           

          By the way, it looks like this is a problem that has been asked before, but I'm not sure how much the users were satisfied with the example provided as an answer: it does appear that it contains a column that was incorrectly totaled. Please check this out:

           

          Grand Total with Table Calculation in Blended Data