topic Re: AVERAGE in DAX Commands and Tips

AVERAGE

rwong1 — Thu, 17 Mar 2022 04:10:06 GMT

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:

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 04:30:32 GMT

The NFE amount is created from a measure.

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 05:17:58 GMT

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

AverageNFE = AVERAGE( TableName[NFE])

And use that in a line chart.

Re: AVERAGE

amitchandak — Thu, 17 Mar 2022 05:31:52 GMT

rwong1 , Assume you need Avg of measure

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

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 14:43:32 GMT

Hi,

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

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 15:06:33 GMT

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.

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 15:53:01 GMT

Hi,

I just tried that and got the following error:

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 15:54:37 GMT

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

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 16:00:14 GMT

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/

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 16:07:34 GMT

It still didn't work. Please advise.

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 16:08:18 GMT

Can you provide the pbix?

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 19:21:17 GMT

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:

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 19:24:37 GMT

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

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 19:25:02 GMT

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

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 19:31:03 GMT

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

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 19:32:29 GMT

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

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 19:36:00 GMT

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

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 19:41:34 GMT

Sure. See below:

Re: AVERAGE

Tutu_in_YYC — Thu, 17 Mar 2022 19:45:02 GMT

That looks good. What about the calculated column?

Re: AVERAGE

rwong1 — Thu, 17 Mar 2022 20:15:28 GMT

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