Forum Discussion

Bryanna's avatar
Bryanna
Icon for Helper II rankHelper II
3 years ago
Solved

Averages to populate in missing dates

Hi,   I have a table where i only have about 1 months worth of data at any given time. I would like to take the average of these values and have the average populate for Jan 2023 to current date ak...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Bryanna 

    I expected that. It's ALLSELECTED that is doing the stange things 😅

    Please try

    Measure2 =
    VAR AverageValue =
    AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
    RETURN
    SUMX (
    VALUES ( 'Calendar Table'[Date] ),
    COALESCE ( [Manual Appts Calc], AverageValue )
    )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Bryanna 

Please try

Measure2 =
SUMX (
VALUES ( 'Calendar Table'[Date] ),
COALESCE (
[Manual Appts Calc],
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
)
)

Bryanna's avatar
Bryanna
Icon for Helper II rankHelper II
3 years ago

Thank you! So looks like it summed correctly, however now the values (averages) are missing on the days where they were populating previously.

 

Measure 3 is the original measure you provided and test manual inbounds is the new one trying to sum all of these values together. My goal is to have values populate for every date and the total be the sum of all of these together.

 

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Bryanna 

    I expected that. It's ALLSELECTED that is doing the stange things 😅

    Please try

    Measure2 =
    VAR AverageValue =
    AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
    RETURN
    SUMX (
    VALUES ( 'Calendar Table'[Date] ),
    COALESCE ( [Manual Appts Calc], AverageValue )
    )

    • Bryanna's avatar
      Bryanna
      Icon for Helper II rankHelper II
      3 years ago

      Yay, that worked!! thank you so much!