how to calculate Average from columns from different tables

[stribor45]

I have two tables and each of them holds the data from different years (2023 and 2024).  I would like to create a measure that would calculate the average of column A from each table. so for example, if Table A is for 2023, Table B is for 2024 I can do the following:

(AVERAGE(TABLE_A[A]) +  AVERAGE(TABLE_B[A])) / 2

problem with this is that sometimes users might select dates that don't spill into other tables which skews the results. Say if my calculated average for July 2023 is 12 this formula would give me a result of 6 instead of 12. 

Any suggestions?

[Anonymous · Accepted solution]

Hi @stribor45 

Thanks for the reply from aduguid .

My sample:

Slicer table:

TableA:

TableB:

Create a measure as follows

Measure = 
VAR _1 = CALCULATE(MAX('TableA'[A]), FILTER(TableA, [Date] = SELECTEDVALUE(Slicer[Date])))
VAR _2 = CALCULATE(MAX('TableB'[A]), FILTER(TableB, [Date] = SELECTEDVALUE(Slicer[Date])))
RETURN
IF(_1 = BLANK() || _2 = BLANK(), (_1 + _2) / 1, (_1 + _2) / 2)

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

I'd use power query and append the tables to get it into one recordset. 

[stribor45]

Yes i was hoping to use DAX

[aduguid]

Try this measure 

Combined Average = 
VAR AvgA_2023 = 
    CALCULATE(
        AVERAGE(Table_A[A]),
        NOT(ISBLANK(Table_A[A]))
    )
VAR AvgA_2024 = 
    CALCULATE(
        AVERAGE(Table_B[A]),
        NOT(ISBLANK(Table_B[A]))
    )
VAR CountTableA = 
    CALCULATE(
        COUNTROWS(Table_A),
        NOT(ISBLANK(Table_A[A]))
    )
VAR CountTableB = 
    CALCULATE(
        COUNTROWS(Table_B),
        NOT(ISBLANK(Table_B[A]))
    )
VAR TotalRows = CountTableA + CountTableB

RETURN
    IF(
        TotalRows = 0,
        BLANK(),
        (IF(CountTableA > 0, AvgA_2023, 0) + IF(CountTableB > 0, AvgA_2024, 0)) / 
        (IF(CountTableA > 0, 1, 0) + IF(CountTableB > 0, 1, 0))
    )

[stribor45]

I am getting 0 as a result