Splash during load based on measure results

      Splash during load based on measure results

      Hi all.

      We are trying to achieve a special behavior, we need to calculate costs based on a general data and splash it according to a measure, for example, we have a general cost that is provided at a level "Al branches", "Year 2014" and "All Months" now, for every branch we need to splash the general cost according to the sells volume that every one get on every single month, for example.

      Input data: 300,000 for all branches, year 2014 and All Months

      Sells on January:
      Branch 1: 1,000,000
      Branch 2: 500,000

      Sells on February:
      Branch 1: 200,000
      Branch 2: 600,000

      Results for January costs:
      Branch 1: 100,000, because we have 2 months loaded (300,000 / 2) = 150,000 and branch 1 represents 2/3 of the total sales in January
      Branch 2: 50,000, because we have 2 months loaded (300,000 / 2) = 150,000 and branch 2 represents 1/3 of the total sales in January

      Results for February costs:
      Branch 1: 37,500, because we have 2 months loaded (300,000 / 2) = 150,000 and branch 1 represents 1/4 of the total sales in February
      Branch 2: 112,500, because we have 2 months loaded (300,000 / 2) = 150,000 and branch 2 represents 3/4 of the total sales in February

      Is there any way to do this during ETL loads?

      Thanks in advance, regards.