4 Replies Latest reply on Jan 8, 2013 5:31 PM by Bibhu Chaudhury

# how to add calculations to a subtotal row?

I would like to add subtotals to my report which contains some calculated percentages, but doesn't seem to work. The "type X user%' column should be calculated instead of being a sum for the partition...so my logic should be

IF sub total row flag = Y then use regular calculation [Type X User %] instead of WINDOW_Sum([Type X User %]). Then the values in red below would be 770/1700, 80/150 instead of the >100% values currently shown. I am not sure how to implement this - any help is greatly appreciated.

 Region Country Type X Users Total Users Type X User% Europe Germany 70 600 12% France 400 500 80% Ireland 300 600 50% Total 770 1700 142% Asia China 10 50 20% Japan 20 50 40% Singapore 50 50 100% Total 80 150 160%

Thanks!

Bibhu

• ###### 1. Re: how to add calculations to a subtotal row?

Depends on how your data is setup.

I assume you are doing something along the lines of a calculated field for Type X users?

eg. sum(if usertype = 'x' then [users] else 0 end)

And total users is just a sum([users])

You can create the percentage without the window sum that will then calculate correctly on that row too

eg. Type x user % = sum(if usertype = 'x' then [users] else 0 end)/sum([users])

If not, can you give us more detail on your data structure?

• ###### 2. Re: how to add calculations to a subtotal row?

Hi Nathan, Thanks for your reply. The data structure is very simple:

 Region Country Type X Users Total Users Europe Germany 70 600 Europe France 400 500 Europe Ireland 300 600 Asia China 10 50 Asia Japan 20 50 Asia Singapore 50 50

I want to group by Region and subsequently add subtotals.

Best,

Bibhu

• ###### 3. Re: how to add calculations to a subtotal row?

Cool. Well that makes life even easier. Just create the calculated field to be sum(Type X Users)/sum(Total Users). put that on your columns shelf and your sub totals will update correctly. The window sum brings in some context issues that aren't needed in this case.

Hope that helps.

• ###### 4. Re: how to add calculations to a subtotal row?

Thanks Nathan. Adding the sub totals works fine for raw numbers but screws up the percentage columns on the total rows. I think I found the solution, I added subtotals from the menu and then added are few calculated fields:

1) TotalFlag ->> If MIN([Country]) = MAX([Country]) then 0 Else 1 End

this flags the row if its a subtotal, grandtotal field etc.

2) Converted % Base ->> [Converted Users]/[Total Entitled Users]

this is for regular rows

3) Converted % ->> If [TotalFlag] = 1 Then SUM([Converted Users])/SUM([Total Entitled Users]) else SUM([Converted % Base]) End

This knows to not sum up the percentages if its a total kind of row.