I have a Fact table – HHData which contains Electricity Consumption - and a dimension table – Emissions Factors containing conversion factors by year (2019 and 2020) for Greenhouse Gas emission. For a given year (or any month within it), I want to multiply the Consumption (kWh) by the conversion factor. I have set up a Key column in each of the tables (1 for 2019 and 2 for 2020 and related the tables using this. For completeness, there is also a Date table. I thought it would be easy but I am clearly missing something. I can only pick up the sum of the Factors (meaningless), the Min, Max, or Average.
Here is my model view.
The emission factor in Emissions Factors is column Value and the consumption in HHData is column kWh
Can anyone help?