cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ehahn
Frequent Visitor

average of a custom measure in a time series

I have four investment accounts: A,B,C, & D. I have monthly balances for these accounts stored in a table BalHistory. I Have also created a calculated measure [% Change] = divide([Total Balance], [Prior Month Balance], blank())-1. I then addded [% Change] to a matrix to show the month over month change in balance.

 

NOW ---- I would like to find the average of the [% Change] by month. Essentially the average of my calculated measure. Current Matrix.png

 

ANY THOUGHTS????

2 ACCEPTED SOLUTIONS

I think a simple piece of code like this should do the work:

 

AVERAGEX ( VALUES ( BalHistory[Household Date] ), [% Change] )

I am assuming you have monthly data, this code is computing the daily average but, as you are likely to have one row per month, the two values are the same.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

 

Alberto Ferrari - SQLBI

View solution in original post

It is likely you need to do a MEDIANX ( ALL ( Household[Date] ), ... ), in the current filter context you only have one date visible and the median is always the same vale as the average.

 

 

It is a very common mistake for DAX newbies to make confusion with iterators, but I cannot help to warn you: spend some time to learn better the basics of filter context, row context and context transition with books, videos or whatever available to you. If you iterate over ALL you get some value, but it might not be the exact value you need at all levels of aggregation.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

View solution in original post

4 REPLIES 4

I think a simple piece of code like this should do the work:

 

AVERAGEX ( VALUES ( BalHistory[Household Date] ), [% Change] )

I am assuming you have monthly data, this code is computing the daily average but, as you are likely to have one row per month, the two values are the same.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

 

Alberto Ferrari - SQLBI

Thanks for your help - this worked.

 

Now, I wanted to compare this to the median so I created the same measure using MEDIANX

 

MEDIANX ( VALUES ( BalHistory[Household Date] ), [% Change] )

 

however this new measure returns the same value as my AVERAGEX function.

 

any ideas as to why the median is showing the same results as the average?2017-10-04_10-34-04.png

It is likely you need to do a MEDIANX ( ALL ( Household[Date] ), ... ), in the current filter context you only have one date visible and the median is always the same vale as the average.

 

 

It is a very common mistake for DAX newbies to make confusion with iterators, but I cannot help to warn you: spend some time to learn better the basics of filter context, row context and context transition with books, videos or whatever available to you. If you iterate over ALL you get some value, but it might not be the exact value you need at all levels of aggregation.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

Thanks again for your insight!

 

I will be sure to continue to watch your youtube videos.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors