Spotfire - Finding Percentage of Subtotals

Question

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

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

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%

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

Answer 1

You didn't go wrong anywhere but your subtotals aren't going to work out how you want. The Subtotals are calculated by Spotfire automatically on the underlying grouped data. So in this case, the subtotal is going to be for the first node of your hierarchy (group). i.e. Groups 1 and 2. You aren't going to be able to change it with your current layout. As you know, subtotal is a portion of the grand total. Grand total is for the entire Cross Table, which will be 100% no matter what your data is. Thus, it's impossible for the sums of 2 or more subtotals to be > 100%. Your subtotal is the SUM of the values calculated off of this formula:

Sum([Sales]) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])) as [% of Total]