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?
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.
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.
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.
A few threads that helped me reach this answer: