Forum Discussion

magnus_b's avatar
magnus_b
Icon for Advocate II rankAdvocate II
5 years ago
Solved

Calculate product attach rate

Hi,   I need some advice on how to properly model / calculate the product attach rate, for the following use case:   A retailer sells computers and insurances for the computers. Each category of ...
Need Help
  • magnus_b's avatar
    magnus_b
    5 years ago

    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:

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

magnus_b 

 

The only problem with this setup is maintenance and understanding. If you take a look at my model, things are straightforward and very flexible (not to mention will be fast no matter how many rows the fact table has). Your model can decay very quickly over time and is overly complex. Later, please don't tell me I did not warn you!

 

As usual, the old rule stands: The fact that you can do something, does not mean you should.