I have the following issue;
I have a fact table with tonnage from different facilities and different months.
I have another table which contains the facilities and an energy factor per facility.
The desired output is a dynamic measure showing the average energy factor by ton.
So “sum of tonnage by facility multiplied by energy factor” divided by “sum of tonnage”
I made the calculation however the output is static and not influenced by the date when added into the visual.
I believe this is a rather easy issue but I fail to see what is wrong as both sums/sum measures are affected by the date but then the next measure is not.
The issue here is that you only have two dates, January 1 and February 1, in your fact table. Therefore the way you have your measures structured, the visual will only show non-null observations on those dates.
If you’re looking to spread the value across the dates in between those two endpoints, you will need to implement an explicit allocation methodology within your measures or within Power Query. If you can provide a mockup of the specific result you’d like to see, I can show you how to implement that.
Also, a couple of other items of note:
you need to mark your date table (“invoked function”, and I recommend changing the name on that to Dates) in order for time intelligence functions to work properly.
in your visual, your date fields should come from your dimension table, not your fact table
whenever you are dividing two measures, use the DIVIDE function rather than the “/”. This will prevent an error when the denominator manager evaluates to zero.
Thanks for the feedback Brian;
In my original file I have much more data spreading multiple months, just updated the example file according to your inputs.
I noticed during this that my initial calculation seems to be the issue, it takes the total sum of the factor from the mapping table, however I need the measure to take the sum tonnage of facility A multiplied by the factor for A from the mapping table, then tonnage of B multiplied by energy factor for B and so on and then sum up the total, so it has to be an iterating function of some form and not SUM.
