How to use grand total in custom expression for spotfire?

Question

I would like to calculate the % of each item using the grand total of qty. Below an example. Any help is appreciated. Can't figure this out...

There's 2 tables.

Below table contains the total qty for a product:

Product	Current_Qty
Product A	1,000
Product B	2,000

Below table contains qty for items in the product

Product	Product_Item	Qty_sold
Product A	Item A1	50
Product B	Item B1	100

Below is the cross table that I have created but not what I wanted The custom expression for % = sum(Qty_sold for Item)/ (sum(qty_sold for Item) + sum(Current Qty))

Item A %	Item B %
50/(50+3,000) = 1.64	100/(100+3,000) = 3.26

I would like to calculate the % of item sold over the grand total of the qty as below:

Item A %	Item B %
50/(150+3,000) = 1.59	100/(150+3,000) = 3.17

Please help thanks.

Answer 1

Since you are splitting your cross table by item, I cannot think of a way to sum up over items. But you could create two calculated columns:

table 1: [Total_Qty] as Sum([Current_Qty])
table 2: [Total_sold] as Sum([Qty_sold])

then the expression for your cross table would be:

Avg([Qty_sold]) / (Avg([Total_sold]) + Avg([table1].[Total_Qty]))

I had to add Avg(..) because Spotfire wants aggregations.