Summing grand totals from different sheets

For today I have a ‘how-to’ regarding summing up the gross totals from two different calculations on two sheets with different filters (possibly) applied.

Assume your first sheet looks like this. I have randomly filtered to ship date years 2021 to 2023.

and the second one like this, filtered based on order date (=2021). Also, I have changed to measure just for emphasis.

So now what we want to do is to show the sum of both grand totals (1,347,375 + 9,837 = 1,357,212) on a third sheet (we might show it wherever, but we will go with a third sheet here).

Solution

It is actually quite easy to achieve, all we need is in this case two table calculations (because we are using two different measures. If it was the same measure, we could just use one).

//Grand total Sales
window_max(total(sum([Sales])))

Now we repeat the above but for our second measure

//Grand total Quantity
window_max(total(sum([Quantity])))

Put these on the detail marks on the respective sheets and click “edit table calculation”.

Choose “Specific dimensions” and click both dimensions, i.e. put both dimensions per each sheet into the respective total calc’s addressing.

Now, what we need is a means to sum up these two values.

The problem is, we cannot do this from one sheet.

The solution: use parameters and a sandboxed extension.

Start with creating two parameters, I’ll call them p.take_val_1 and p.take_val_2

Make them float or integer, depending on what your above calculations are and set to “allow all”. Be aware, having the same data type in the parameter and the calculation is mandatory!

Now, in case you haven’t done so yet go and create a dashboard. Put both your calculation sheets on the dashboard.

Next, add extension. Select “data driven parameter” extension.

The moment you click “add to dashboard” you will be prompted with setting up the extension.

Select your first parameter, the sheet that shall feed the parameter and the field which is our just created grand total sales calculation

However, this is not enough.

Go to “Options” and select “Filter based on worksheet selections”.

Now repeat the above steps by adding a second extension and do the same for the other calculation and the second sheet.

Create a third sheet and a calculation

//Sum of grand totals
p.take_val_1 + p.take_val_2

Put this onto your third sheet’s text mark and you are basically done.

Our grand total calculations will update based on the filters we set thereby feeding the parameters and these will feed our final calculation.

And that is it for today. I hope you find that useful. Find the workbook here.

As always appreciate your feedback.

Until next time.

Steffen