# Spotfire - Finding percentage of subtotals

I'm trying to turn a cross table that looks like this

 Group Product Sales 1 A 20 B 40 C 30 2 D 50 E 30 F 60

into a table which shows the subtotals and percentage over each Group like the example below

 Group Product Sales Percentage 1 A 20 22% B 40 44% C 30 33% Subtotal 90 100% 2 D 50 36% E 30 21% F 60 43% Subtotal 140 100%

Where the percentage is the sales of each product divided by the total sales in each group, so for Product A = 20 / (20+40+30) = 22%

So far, I've managed to use Spotfire built-in subtotal function and the following expression to almost achieved the table I want

Sum([Sales) / Sum([Sales]) OVER (Intersect(Parent([Axis.Rows]),All([Axis.Rows])))

but the only problem is that the percentage for my subtotal row doesn't seems to equal to 100%, instead it is taking the subtotal sales divided by the grand total for all groups, meaning 90 / (90+140) = 39%, so I end up with a table like this:

 Group Product Sales Percentage 1 A 20 22% B 40 44% C 30 33% Subtotal 90 39% 2 D 50 36% E 30 21% F 60 43% Subtotal 140 61%

Can anyone advise me where did I go wrong and how do I resolve this? Thanks

## 1 Comment

Anyone knows how to solve this?

DLWEB - Aug 08, 2016 - 6:34pm ::
+ Add a Comment