Hi everyone,
I'm using power PowerBI to evaluate the results of multiple forecast providers for a specific variable.
The model I created has the following tables:
- Fact table of the measured variable, with hourly timestamp, the asset on which the variable is measured and the value
- Fact table of the different forecast provider, with the hourly timestamp, the asset on which the variable is measured, the provider name and the forecasted value
- Time dimension hierarchy (tables with hour, date, month, etc)
- Asset dimension, with asset and related properties
- Forecast provider dimension, with the provider properties
I want to create a measure that calculates the AverageX of the difference between the forecast and measured value and divide that amount by a property of the asset.
The solution I found so far is to join, in the forecast fact table, the measured value by hour and asset and the asset property via power query, but I don't like this solution and I would like one that uses only DAX, while ensuring good performance.
I tried to use the CALCULATE function in conjuction with the RELATED one, but I wasn't able to find a valid solution.
Any suggestion would be strongly appreciated.
Thank you
