average of a custom measure in a time series

[ehahn]

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. 

ANY THOUGHTS????

[AlbertoFerrari · Accepted solution]

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

[AlbertoFerrari · Accepted solution]

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

[ehahn]

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?

[AlbertoFerrari · Accepted solution]

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

[ehahn]

Thanks again for your insight!

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