I am trying to create a calculation that gives me a Purchase
Likelihood (PL), where PL = Quote Purchase Count (QPC) / Quote Count
(QC).

I have a QC measure that is a distinct count of the quoteID in my fact
table. There can be several fact records with the same quoteID, and
the Quote Count is the distinct count, giving the the total number of
quotes.

I have a Purchase Count (PC) measure that records the number of
purchases for every transaction in the fact table. So, you could have
several PCs for every quote.

The max value of the PL is 1 (can only have a PL of 100%). So, I need
a way to calculate the QPC, which would essentially give me the total
number of quotes where the PC > 0. For example, if I have 3 PCs for a
set of 8 fact records all with the same quoteID, the QPC should be 1
for that set, giving me a PL = 1/1 = 100%

I have tried COUNT(FILTER(QC, PC>0)) to get the number of quotes where
the PC > 0, but I think the filter just returns a set of 1 element,
and the count of that set is 1.

Any ideas are greatly appreciated.
Thanks,
Dave

A couple of approaches that come to mind:

1) If there is a "Quote" dimension, with the leaf level consisting of
unique QuoteID's, then a Count(Filter(.. can count the subset of unique
QuoteID's with purchases (QPC).

2) An alternative with better performance: create a 2nd cube by copying
the original. But add a SQL 'where' fact table filter condition to its
partition: PC > 0. You need only the distinct count measure (QC2) in
this 2nd cube. Now combine both cubes in a virtual cube, where QC is QC1
from the 1st cube, and QPC is QC2 from the 2nd cube (only includes fact
table rows where Purchase Count > 0).

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks - the second approach is great with the virtual cube. This will
also allow us to break the purchases into their own separate cube for
additional purchase only analysis.
Thanks,
Dave

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote