Forum Discussion

rogerthat's avatar
rogerthat
Icon for Helper I rankHelper I
3 years ago
Solved

Missing Data Points for Average

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's avatar
    Anonymous
    3 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.