I have two Data tables (Data_table_A & Data_table_B). Both of them are connected using two relations with two fact tables (Fact_table_Date & Fact_table_Time).
I did a visual table to calculate the average error filtering by hour, using a Measure_1:
However, I saw my Measure_1 is not working on the way I need. As you can see, my results on the column Measure_1 is the error for the average I calculated in the columns AVE_table_A & AVE_table_B. Even it calculates the error for the totals (Blue), when my average for that column is different (green).
//Calculates average by hour
VAR _Actual = [AVE_table_A]
VAR _Forecast = [AVE_table_B]
//Calculates the differences between both values
VAR _Error = ABS(_Actual - _Forecast)
//Return the error if the data is not below zero or is empty
OR(Actual <= 0, Forecast <= 0),
What I try to do is :
1. Go to each hour on each day, calculate the average of each table (Because each table have data for each minute), then calculate the error for that hour.
2. Then, do the average of each error on each hour
3. In the final table show the average of the column and not calculate the measure.
Example of the calculation only for two day (Normally I will do it for each month):
P.D: I read this article which explain why I have that result at the end of my table. I understand it. But I require the average on my table, not the other result.