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?