Missing Data Points for Average

[rogerthat]

Hi,

I'm currrently calculating the average for a column in Table 1, however there's data missing and I'm trying to include it

Data Exists for 6, missing for 11.

Average = {5,5,8,6,8,6} -> 5

Is there a way to add let's say if I had additional data from Table 2 into a measure to fully add the correct average values?

Data to include would the days not included, in this case 7, to equal 0.

{0,0,0,0,0,0,0,0,0,0,0} 

So in reality it should be Average = {5,5,8,6,8,6,0,0,0,0,0,0,0,0,0,0,0} -->2.24

Maybe it can't be done but just wondering if its possible.

[Anonymous · Accepted solution]

Hi @rogerthat ,

Due to I don't know your data model, here I create a sample to have a test.

Average =
AVERAGEX (
    UNION (
        SELECTCOLUMNS ( 'Table 1', "Value", 'Table 1'[Value] ),
        SELECTCOLUMNS ( 'Table 2', "Value", 'Table 2'[Value] )
    ),
    [Value]
)

Result is as below.

Best Regards,
Rico Zhou

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