Subtotal and grand total

#1

Hi all,

I am having some issues with subtotals and grand totals. Currently I have data that rolls up to a daily total. The data set has each day broken up to 24 hours. What I am trying to do is once my data is summed up to daily value, showing the average by group. Currently the grand total is summing. I would like to see this averaged.

I tried to change the grand total calculation to average but it averages all the rollups.
First image shows the hourly summary, three buckets add up to a net. This then adds up to the day
Rollup%20Day

Second image shows all the days. The -151 is a sum off all the days. I would like this to be an average of all the days.
Rollup%20Group

#2

Hi Patrick,

In this case, you can use the custom total calculation - https://www.dundas.com/support/learning/documentation/analyze-data/using-a-custom-total-calculation
In the custom total calculation you can add the formula that based on your requirement. This formula would be used for the total calculation only for a particular measure to which it is added to. In the below example I have added the custom total calculation to calculate the average instead of sum:
image

Regards,
Pankaj

#3

Thanks Pankaj,

So I had seen this previously. Hopefully using the table I can get a bit more understanding.

Here we have 12.50% in row 1. When I try to create a custom formula to say an average it changes this number as well. What I am trying to do is keep the percentages in this the same when I create a custom totals formula to average.

image

#4

Hi Patrick,

As per my understanding, you have a calculated formula measure in your metric set. So, are you changing the formula for the formula measure itself, or are you add a custom total calculation to the formula measure?
To add a custom total calculation for a measure, you will have to click on the edit icon of the measure, then scroll down to Totals Calculation Rules (Rows):
image

Click on the Define totals calculation rule formula. This will open the formula bar, where you can add the custom formula that will only be used for total rows. This should not affect the overall formula the row levels.

Can you please check this and let me know if this helps?

Regards,
Pankaj

#5

Hi Pankaj,

Please see response below.

Data at the hourly level is being summed from buckets to a net.
Total in yellow showing as sum.
The totals 10.67, 8.667, 9.35 are the way I would like to portray the net balances.

image

After creating a custom total of AVG($Volume$), (Column C in image), every value in column C is now showing as an average.

Ideally I would still like to keep the hourly rollups a sum of the two columns A and B and the final total at the bottom I would like an average of the A+B in the group. (In the image above AVG(10.67, 8.667, 9.35, etc.)

image

#6

One thing you could do is create a nested formula situation. Basically - create a column D which will be invisible and reference column C (or be functionally identical to column C). Then set that column D to aggregate to average so its behind-the-scenes grand total will be what you want.

Then in C’s custom total, you can reference the invisible D column.