Hi everyone,

I'm trying to achieve a Product Affinity analysis using AS. The idea is
to determine how many times a particular product y bought with every
other one in the same transaction (or ticket). Since doing this using a
table containing all the tickets would be very expensive, I came up with
the idea of making an "Affinity fact table" that in it´s most basic form
has three fields:

1. Item
2. Analysis Item
3. Affinity Count (times bought together).

This table holds every possible combination and how many times it happened.

Now, in order to calculate a percentage of affinity (the percentage
every pair of products are bought together), I also need another metric
which is sale sales count (ticket count) for every item. So I have
another fact table called "Sales Count".

1. Item
2. Sales Count

With this two I can do % Affinity = Affinity Count / Sales Count. When I
pick a particular Analysis Item.

I was trying to make two cubes and then combine them in a Virtual Cube
so I could make this calculation but unfortunately, Whenever I filter
using my "Analysis Item" Dimension, the Sales Count has null values in
the grid even though I have my Item dimension on the rows, hence I can't
make the "% affinity" metric work.

I was thinking may be a custom rollup formula could help but I'm a bit
lost here.

Any help is appreciated. I'd be happy to extend my explanation if necesary.

Ricardo Sada