I have a somewhat complex Template I’m working on which uses multiple data views pulling information from 2 different cubes. I’ve run into an issue where I’m trying to create a subtotal line that totals 2 data views from 2 different cubes. I was hoping I could use a simple SUM() formula, but the references become inaccurate even if I add blank rows to a data view and place the formula there.
The solution I’ve settled on, for now, was to use a custom naming convention for the row headers in the 2 data views and utilize a VLOOKUP() formula to pull the values from the specific rows I need to subtotal. I was wondering if anyone else has encountered a similar problem and if so what their solution was?
Hi Cory,
In cases where you want to sum up a total from dynamic members (single or multiple cubes), the way to do that is by using an anchor.
For instance in a template, in Col B you have the amounts where
row 1 is a dynamic member (viz. Revenue.leafdescendants) from Cube 1
the second data view is below that has,
row 4 is a dynamic member (viz. Salaries.leafdescendants) from Cube 2
row 5 >> you would create a ‘row below’ in this data view
in row 6, you would use the formula SUM(B1:B5).
Notice that even though your data is only until Row 5, using a blank row in the data view (i.e. row 5) anchors the formula and ensure that when the dynamic member expands, it is included in the summation.
Look into the Sum with the offset feature. This only works on the web client but it enables you to use your formula row as the anchor. you might be able to create a separate sum for each data view that you hide and add those to get a subtotal.
I’d tried anchoring the formula by adding a blank row, but it didn’t work properly. I don’t know for sure why, but my theory is because the data views I’m summing are being pushed down by further dynamic data views above them. Thanks for the suggestions, though!
I have also tried anchoring the formula by summing onto a blank row above, but as the data pushes down the row, the bottom row formula doesn’t adjust to capture the additional rows being added due to the dynamic formula.
I also tried using the dollar sign to anchor and this also doesn’t update with dynamic rows.
How would the offset formula be used with dynamic data?
You’re right–the anchor needs to be below the last line, because that way it gets pushed down below all of the dynamically created rows. (If you’re achoring a column formula, put the anchor to the right of the last expanding cell.)
I realize we are 2 years after the original post, but in thinking of @cory.lapaz’ post from way back in Nov’20, I think his problem was the blank row he inserted, assuming he extended the data view area down by a row. By inserting a blank row inside the data view area, dynamically created rows may actually get added below the added row (depending upon the structure of the data view).
So, when summing a dynamic range of columns, sum from the top row in the range to the row below the bottom row of the range.