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

(1) Answer

Login