Calculations from 2 fact tables

  • leehbi


    Points: 2874

    I have a situation where there are 2 facts tables representing SalesLeads / Sales.    These facts are semi-independent i.e. some SalesLeads will lead to Sales but Sales can be made with no SalesLead

    I need to calculate  Sales/SalesLeads for measures such as count, revenue etc..

    It's normally a no no to have cross fact calculations hence I'm thinking of a separate fact table containing the measures for both tables with the same keys for dimensions.   This is a lot of data duplication hence I'm wondering if this is the recommended approach.

    The model will be used by analysts in Power BI hence the calculations will be otherwise done with DAX which will be expensive as there will be a calculation over 2 fact tables for the numerator/denominator.    If I combine the fact tables there will be less expensive DAX.

    • This topic was modified 3 weeks, 3 days ago by  leehbi.
  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • pietlinden

    SSC Guru

    Points: 62424

    I would leave the tables separate. You can write a query/function in DAX to do the counts. If they're separate, it makes creating basic measures much easier.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply