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.

     

rwong1's avatar
rwong1
Helper III
4 years ago

The NFE amount is created from a measure.

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

       

       

      • rwong1's avatar
        rwong1
        Helper III
        4 years ago

        In this case, the average from the measure should show 6,067,963.98 if the formula worked properly.