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!