1 Reply Latest reply on Feb 12, 2017 11:09 AM by Kevin Coles

    Totals when using a Calculated Member (Cubes)

    Kevin Coles

      HI there,

       

      I'm a novice to Tableau and I have the double whammy of using the Deltek Vision Performance Management version of Tableau which is packed with Deltek Vision Performance Management Module. This is somewhat limited version that is designed to work with the Deltek Vision cubes. Since it's a cube I'm not sure I can upload a workbook but maybe someone can assist me in how to do that as well.

       

      I'm developing a tabular dashboard for a client and have made excellent progress so far. I'm capturing a slew of common financial metrics used in the Engineering and Architecture space and displaying them using Fiscal Month columns and metrics as rows. The issue I have encountered is with totals and I have read a ton on various issues and how to overcome them but I think my situation is a bit unique.

       

      The General Ledger cube I'm working with has lots of Fiscal dates/periods to work with and that's all good, however there is not built in way to filter on the status of a fiscal period. My client only wants to display data from closed fiscal periods for the simple reason that the metrics and the row totals get completely skewed when you have incomplete info from an open accounting period. So I needed a way to only capture closed periods. This was simple enough by using a custom SQL query to pick up the "closed" flag for a period and join it by fiscal period to the cube. All good except that it breaks the Totals on some specific metrics. All of the ones in question are using either a Calculated Measure or calculated field.  One example which can be seen in the screen shot attached is:

       

      Contribution Margin = Net Revenue / Total Revenue -  shown as a percentage

       

      As soon as I have the Closed Period only filter added then the totals no longer work and I get a data blend error. I have looked into trying a running sum and using an index to hide all but the last column however, this doesn't work on the Calculated Member. It will work fine on the two Dimensions (Net Rev and Total Rev) but not the calculated member.

       

      I have attached a few screenshots and I can grab anything else that would be helpful just not sure if I can upload a workbook. I think this is a fairly unique scenario but I also believe the issue is a common one from what I have read about totals and how they work. Hoping one of the experts out there can point me in the right direction.


      Thanks in Advance!

       

      Kevin Coles

        • 1. Re: Totals when using a Calculated Member (Cubes)
          Kevin Coles

          I just added an Excel mock up of my problem. IN the mock up Net Revenue and Total Revenue are measures from the cube. Contribution Margin is a Calculated Member: Net Revenue / Total Revenue. I don't actually show the Revenue rows in the final dashboard but I included them in the mock up to help visualize. The calculation works fine on a monthly basis however Grand Total using sum obviously produces the incorrect result on Contribution Margin. I know this is expected behavior, however I am not sure how to go about this.

           

          I have tried making a separate worksheet for totals only by using a running sum table calc but that didn't seem to work either. I'm sure this is possible somehow but I'm stumped! Hope someone can help as I need to do this same thing for 7 different metrics.

           

          Thanks,


          Kevin