AVERAGE

[rwong1]

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 · Accepted solution]

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]

Hi,

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

[Tutu_in_YYC]

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]

Hi,

I just tried that and got the following error:

[rwong1]

The Date is a date type, not a text type.  I checked.  

[Tutu_in_YYC]

Use Year-Month as data type Text and visualize that.

For future reports, i recommend having a separate date/calendar table
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[rwong1]

It still didn't work.  Please advise.

[Tutu_in_YYC]

Can you provide the pbix?

[rwong1]

Is there any other way to do this besides sharing the file?  It has confidential info in it.  

[Tutu_in_YYC]

See if this helps ( or if this is what you are trying to achieve )

[rwong1]

Yes exactly.  How do I get to this?  My date format is a little different.

[Tutu_in_YYC]

Can you provide a snapshot of just your [Posting Date_1] column? I think that is your issue here. I assume it will look something like this : 3/16/2021-03 and it is not a date type

[rwong1]

Sure.  See below:

[Tutu_in_YYC]

That looks good. What about the calculated column?

[rwong1]

Sorry I also want to average it by client which is equivalent to your column called "person."

[Tutu_in_YYC]

Just add client column into the table

[rwong1]

Ok, now there is no error.  Not sure what happened.

[rwong1]

Can you provide me with the formula for the measure?  

[Tutu_in_YYC]

AverageNFE = AVERAGE( TableName[NFE])

[rwong1]

But the NFE is a measure.  I won't find it in a table.