Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.