Forum Discussion
Advocate IICalculate product attach rate
Anonymous daxer-almighty
I was able to solve this by creating a new dimension for insurance products:There are two different (both inactive, since USERELATIONSHIP() won't work unless they are both inactive for some reason) realtionships between the insurance dimension and product dimension:
- CategoryID -> CategoryID, to calculate the number of compatible computers sold per insurance product- InsuranceProductID -> ProductID, to calculate the number of insurances sold
The measures then become:
- Insurances sold = CALCULATE(SUM('FACT Sales'[Quantity]), USERELATIONSHIP('DIM Insurance'[InsuranceProductID], 'DIM Products'[ProductID]), NOT(ISBLANK('DIM Insurance'[InsuranceProductID])))
- Computers sold = CALCULATE(SUM('FACT Sales'[Quantity]), USERELATIONSHIP('DIM Insurance'[CategoryID], 'DIM Products'[CategoryID]), NOT(ISBLANK('DIM Insurance'[InsuranceProductID])))
- Attach rate = DIVIDE([Insurances sold], [Computers sold])+0
Which produce the expected result, while keeping all sales in a single FACT table:
Solution Sage
Here's a file to get you started. I didn't know all the details so I had to invent some of the rules. You need to understand as well that since one has to know which insurance belongs to which product on each individual invoice, the products that are associated with insurances have to have entries in the Sales table where their quantity is 1. This is because the table keeps associations between products and insurances as well as products and invoices. If, on the other hand, you've got many copies of the same product on the same invoice and they are not insured, then you can aggregate them into one entry in the table. This is why there's a field for the quantity of products but there's no such field for insurance products (each line means exactly one insurance product). Note as well how the measures are coded. The quantity of products uses SUM over the quantitiy field but the quantity of insurance products uses COUNTROWS. It's important to understand this and the reason behind it.
Of course, you can have different rules but then you'll have to change the measures accordingly.
magnus_bHi daxer-almighty !
Thank you so much for the file! It is helpful, but it seems the logic is a bit different than how my actual data is. In my Sales table, both computers (and other products) and insurances are separate line items, like this (I have used names instead of id's here for clarity):Caluclating the overall ratio of insurances sold to computers is straight forward:
- Insurances sold = CALCULATE(SUM(Quantity), Category = "Insurance")
- Computers sold = CALCULATE(SUM(Quantity), Category IN {"Laptops", "Desktops"})
- Attach rate = DIVIDE(Insurances sold, Computers sold)
The problem arise when I need to have a measure that can work on the category dimension, i.e. calculating the attach rate per category of computers, because the "Insurances sold" measure will not have the relation between insurance product id and category id.
I might be using the term attach rate wrong here - I am actually after the ratio of sold insurances versus compatible computers sold. Sorry if that was confusing. So it is actually not necessary to associated each insurance sold with the correct computer line item, only to have a relation on the category level.