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:

     

     

     

magnus_b's avatar
magnus_b
Icon for Advocate II rankAdvocate II
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: