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:

[Tutu_in_YYC]

What is the DAX syntax that you have for NFE?

[rwong1]

It's the below:

[Tutu_in_YYC]

Alright, it is more complex than expected. What is in [Raw Loan Amount] measure (the DAX syntax)?

[rwong1]

Sorry.  I stepped out.  Here it is:

[Tutu_in_YYC]

Try this:

AverageNFE = AVERAGE( 'Raw data tables'[amount_6])

[rwong1]

It didn't work because these are daily balances which are cumulative.  Is there another way?

[rwong1]

Please see below:

If it worked, January's average balance should be 6,067,963.98.

[Tutu_in_YYC]

Hi rwong1,

If it is daily accumulation, what kind of monthly average are you looking for?

Eg. If in Jan 2022, daily transaction is 1 dollar. The average for Jan 2022 is 1 dollar even though the sum is $31.

Or do you have multiple transactions in a day that needs to be summed?

[rwong1]

No if it worked January's average should be 6,067,963.98.  The sum of all those numbers in January is $188,106,883.30.  You take that and divide it by 31 days in January and you get the 6,067,963.98.  I'm trying to get Power Bi to produce monthly average of the NFE columns.  Each day's balance is listed in the NFE column.  

[Tutu_in_YYC]

Lets try this:

Create this calculated column in 'Raw data tables'

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

Then create this measure:

AverageNFE =
AVERAGEX(
  SUMMARIZE(
  'Raw data tables',
  'Raw data tables'[Date],
  "NFE", 'Raw data tables'[amount_6]
  ),
  [NFE]
)

Your date column could be [Posting Date_1] if it is in the 'Raw data table'

Then plot the table:

[rwong1]

I can't do the Raw data tables [amount 6].  It won't let me choose it.  Only options are measures within that table.

[Tutu_in_YYC]

So you cant create a measure? What tables/columns do you have access to? Thats odda and sounds like there is Object-Level-Security in place or some sort of other limitations

[rwong1]

Please see what I was talking about:

[Tutu_in_YYC]

Remove what i have crossed out in the diagram with red ( that includes the last bracket in line 9, there should be only 1 close bracket in line 9)

[rwong1]

I did that.  It still didn't take.  It's not picking up amount 6 from Raw data tables as that's still underlined.  I don't know why.

[Tutu_in_YYC]

my mistake! i missed a function there

AverageNFE =
AVERAGEX(
  SUMMARIZE(
  'Raw data tables',
  'Raw data tables'[Date],
  "NFE", SUM('Raw data tables'[amount_6])
  ),
  [NFE]
)

[rwong1]

No it didn't work.  Still showing the wrong average numbers.

[Tutu_in_YYC]

Screenshot please.

[rwong1]

[Tutu_in_YYC]

I am missing something here, but without knowing more about your data model and the granularity of the tables, it is going to be hard.

If you could replicate a pbix with no sensitive info, I can definitely take a look at it.

[rwong1]

Okay let me see how I can do it.  The thing is, it's not the average of that amount_6.  These are cumulative balances per day so the average of amount_6 won't give it to me.  I think the formula has to be mixed in with the CUMULATIVE AMOUNT measure.  I just don't know how to do it.