Forum Discussion

rwong1's avatar
rwong1
Helper III
4 years ago
Solved

AVERAGE

Hi,

 

How do I create another table that shows the monthly average of the NFE by month and year?  I then will convert that to a line chart.  Here is the sample data:

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi rwong1 ,

     

    Sample data:

     

    Here's my solution.

    1.Create a YearMonth column and a sort column. The sort column is used to sort the YearMonth column.

    YearMonth = FORMAT([Date],"YYYY-MMM")
    sort = VALUE(FORMAT([Date],"YYYYMM"))

     

    2.Then you can drag the NFE column directly into the table and get the average.

     

    3.Or you can create a measure.

    Measure = CALCULATE(AVERAGE('Table'[NFE]),ALLEXCEPT('Table','Table'[YearMonth]))

     

    The line chart:

     

     

    Best Regards,

    Stephen Tao

     

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

     

47 Replies

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      rwong1 , Assume you need Avg of measure

       

      AverageX(Addcolumns(Summarize(Table, Table[Year], Table[Month]), "_sum", [NFE]), [_sum])

      • rwong1's avatar
        rwong1
        Helper III
        4 years ago

        Hi,

         

        I put in your formula but it's only taking the last day of the month.  How do I do the average for the whole month that pertains to a particular year?  Don't I need inbetween dates function?  See below:

         

         

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    4 years ago

    You dont need to create another table. Just create a measure:

    AverageNFE = AVERAGE( TableName[NFE])

     

    And use that in a line chart. 

  • rwong1's avatar
    rwong1
    Helper III
    4 years ago

    Hi,

    It only picked up the last value.  It did not do the average.  Please advise.

     

     

    • Tutu_in_YYC's avatar
      Tutu_in_YYC
      Super User
      4 years ago

      if you use the function AVERAGE, it will calculate the average. You will also need a Year-Month column to be used in the visual. Create this calculated column in the table:

       

      Year-Date = YEAR([Date]) & "-" & FORMAT([Date], "MM")


      [Date] will be replace by the column in your table that has the date.

       

      Than plot a line chart or a table using Year-Date and the measure you have created.

      • rwong1's avatar
        rwong1
        Helper III
        4 years ago

        Hi,

         

        I just tried that and got the following error:

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi rwong1 ,

     

    Sample data:

     

    Here's my solution.

    1.Create a YearMonth column and a sort column. The sort column is used to sort the YearMonth column.

    YearMonth = FORMAT([Date],"YYYY-MMM")
    sort = VALUE(FORMAT([Date],"YYYYMM"))

     

    2.Then you can drag the NFE column directly into the table and get the average.

     

    3.Or you can create a measure.

    Measure = CALCULATE(AVERAGE('Table'[NFE]),ALLEXCEPT('Table','Table'[YearMonth]))

     

    The line chart:

     

     

    Best Regards,

    Stephen Tao

     

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