Forum Discussion
Helper IIIAVERAGE
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.
Super User
AverageNFE = AVERAGE( TableName[NFE])
Helper IIIBut the NFE is a measure. I won't find it in a table.
- 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?
- 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
Please see below:
If it worked, January's average balance should be 6,067,963.98.
- 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_YYCLets 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.
- Tutu_in_YYC
I think i know what im missing. Tell me, does the cumulative restart every month?
The following assumes year to date cumulation (cumulation doesnt restart every month):
AverageNFE =
VAR _Daysinmonths = COUNTROWS(Date[Date])
VAR _Sum =
SUMX(
Date[Date],
[CUMULATIVE BALANCE]
)
RETURN
DIVIDE ( _Sum, _Daysinmonths, 0)
Make sure you have created this in Date table ( not raw data table) :Year-Date = YEAR([Date]) & "-" & FORMAT([Date], "MM")
Plot Date[Year-Date] and AverageNFE in a table.