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

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

Thanks that worked! How can i now get the sum of these numbers? When its at the day level it populates the average for each day but if i were to change this at a month level its still just taking the average for that day and not summing all of the days in that month.

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