Unanswered: Calculated Measure: Conditional Count from a Dimension
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);
Country Amount Sales Quotes Conversion
England 100.00 10 40 20%
France 120.00 10 20 33%
Spain 80.00 3 6 30%
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 for the response. If I was simply looking to execute SQL against the DW then I guess that would be fine, but I'm afraid it doesn't really get me any closer to a calculated measure. I'm slowly getting my head around MDX, but a few hints in the right direction are what's needed about now.