Firstly; Great videos. A fantastic effort in sharing your knowledge...thank-you.

Now to my problem; I'm new to SSAS and am tainted by years of normalised database development. I have a fact table 'fact_transaction_items' with a currency value 'amount' and a foreign key 'transaction_type_key' and a degenerate dimension 'transaction_no'. I have a dimension table 'dim_transaction_type' with a column 'type' which can contain 'Quote' or 'Purchase'. The fact table granularity is at item level within a transaction.

I need to calculate 'Conversion Ratio' ie. How many quotes result in a purchase?

I'm not sure how I should approach this. I assume that I need to start by getting a count and a conditional count, but of course I need it distinct by 'transaction_no'. I've looked at the calculated measures in AdventureWorks, but they don't seem to accomplish anything similar.

What I'm looking for might look something like this (assuming an additional dimension for client geography);

You will note that Conversion = Sales / (Sales + Quotes). This is because every sales transaction would have originally been quoted so total quotes is in fact quote transactions + sales transactions. This may be easier if you think in terms of Sales being converted quotes and ‘Quotes’ as it appears above being unconverted quotes.

The real issue would seem to be how to get a count and a conditional count.

Any ideas or suggestions would be most appreciated. Thanks in advance.

Thanks so much for your detailed response. I did finally come up with a result that uses the technique you've detailed. I have the further complication that the granularity is actually lower than transaction (or quote), granularity is at transaction item level. I have been able to work with this by doing a count distinct over the degenerate dimension transaction id.

My next challenge is to incorporate attributes which are only at transaction level, for example an admin fee. I'm thinking that the approach is to create another fact table with granularity at transaction level and relate it through the degenerate dimension for transaction id. I'm new to this and not really sure how SSAS will aggregate in this scenario. Assuming that this is the correct approach, should I really be placing count distinct attributes in the higher level transaction grained measure group? I don't want to lose the ability to see a count of transactions that include a given product when I drill down to transaction item level.

Off to do some more exploring....thanks again...