Flexible calculation between forecast and actual tables

[Anonymous]

Hi team,

I'm trying to understand how to manage different fact tables for forecast and actual values flexibly.

Exemplifying my case, I have the following fact tables:

- 2 distinct tables for actual values (listed for some different areas) of 2 different clusters (say A, B)

- 2 distinct tables for forecast values (as above, listed for different areas) of the same clusters (A, B)

- 1 table for a cluster where actuals and forecast values coincide (cluster C, Actual = Forecast)

Tables

My dimension tables are:

- Datetime (granularity)

- Areas

I need to perform different operations with these data, such as:

- calculate the total actual and forecast for each hour

- calculate the forecast error for each hour at different levels (total error: total forecast - total actual; zone specific error: zone forecast - zone actual)

- evaluate forecast sign (< 0 or > 0)

- evaluate relative and absolute percentage errors...

With my poor skills, I used to merge tables, or use poor relationships...Now I see I need a more efficient way to manage this data: what is the best way? Merged table? Calculated table/columns? Only relationships? Could you please help with some suggestions?

I provide a sample Excel and Pbix file.

Excel sample 

PBIX sample 

thanks 

[Anonymous]

Thanks @v-easonf-msft 

But how can I calculate the difference between actual and forecast (where the difference must be evaluated hour by hour, and then summed), once I have a single table?

[v-easonf-msft]

Hi, @Anonymous 

You don't need to create separate actual tables and forecast tables for each cluster.

You can consider adding a new field "cluster" and then append all actual tables/forecast tables together.

Best Regards,
Community Support Team _ Eason