Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bullpro_
Frequent Visitor

Calculate a measure of each step and then do the average of that result

Hello,

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). 

Bullpro__0-1679494865490.png


I did a visual table to calculate the average error filtering by hour, using a Measure_1:

Bullpro__1-1679494986754.png

 


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).

My code:

Measure_! =
//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
RETURN
AVERAGEX(
    VALUES('Fact Table_TIME'[Hour]),
    IF(
        OR(Actual <= 0, Forecast <= 0),
        BLANK(),
        DIVIDE(_Error, Actual)
    )
)



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):

Bullpro__2-1679495891463.png


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.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hello again @Bullpro_ 

please try

Measure_! =
AVERAGEX (
CROSSJOIN (
VALUES ( 'Fact Table_DATE'[Date] ),
VALUES ( 'Fact Table_TIME'[Hour] )
),
//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
RETURN
IF ( OR ( Actual <= 0, Forecast <= 0 ), BLANK (), DIVIDE ( _Error, Actual ) )
)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hello again @Bullpro_ 

please try

Measure_! =
AVERAGEX (
CROSSJOIN (
VALUES ( 'Fact Table_DATE'[Date] ),
VALUES ( 'Fact Table_TIME'[Hour] )
),
//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
RETURN
IF ( OR ( Actual <= 0, Forecast <= 0 ), BLANK (), DIVIDE ( _Error, Actual ) )
)

Thank you again @tamerj1 , It works perfectly.

Let ask again. Can you just explain why the order impacts directly in this case and why we should use the function CROSSJOIN?

Thank you!

@Bullpro_ 

Because you have two fact tables the iteration is required across the crossjoin of the columns of dimension tables. This simulates your table visual 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors