Forum Discussion

rwong1's avatar
rwong1
Icon for Helper III rankHelper 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.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
Icon for Helper III rankHelper 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
    Icon for Helper III rankHelper III
    4 years ago

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