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:
I suggest you use what I created because it's the correct way to model the data. Yours will not suffice. If you have one line per item, even if there are multiple same items (some of them with insurance, others without), then it's even easier. You just cannot store data the way you do because you won't be able to get what you want. My model gets you what you want practically without any effort.
By the way, you can reshape your data using Power Query to look like mine. This is no problem at all.