Average data Quarterly by Category

[Anonymous]

Hello I'm trying to do a simple average on a matrix inside of PowerBI. What I need is for each Advisor group to average the AUM amount by quarter/year. I tried to display AUM as average, but this does not return the correct results.

It appears I need to create a DAX formula for this, but I'm pretty new with DAX. 

Dataset Example 

The two tables are joined by an account number. What I'm trying to get would be the Average of each Quarter by Advisor. If there is only 1 month of data in the quarter the average would be that 1 months value.

I've tried many variations of Calculate with Average/Averagex and different filters but it never returns the correct results. 

[Ashish_Mathur]

Hi,

So you want the average (of the months) in the quarter row for each advisor.  Share the link from where i can download your PBI file.

[Anonymous]

Hello Ashish,

Yes that is what I'm trying to accomplish. Let me know if this link works, I'm not sure the best way to share PBIX files on these forums.

https://1drv.ms/u/s!AuTtSCy5MJidvBmaENppBws4bE54

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hello Ashish,

This doesn't seem to be returning correct averages it is returning the same problem I run into.

It returned Q4 - 2017 12.86M would expect 1.42bn

Expected Results:

2017

Q4 - 1.42bn  (only 1 month of data should return that months value)

2018 

Q1 - 1.48bn

Q2 - 1.63bn

Q3 - 1.70bn

Q4 - 1.63bn

etc...

Is this possible? It should be the three months summed up and divided by 3 but I'm not sure what is happening to cause it to be incorrect.

[Ashish_Mathur]

Hi,

I do not understand your result.  The average for Q1 of 2018 should be the average of 1.33 billion.  This is the exact result my formula throws up.  Why should it be 1.48 billion?

[Anonymous]

In this image is the calculation you did vs AUM

https://imgur.com/kr5GfYr

Your calculation is returning 1.33 million unless I'm not seeing the same as your file. This is from the PBIX file you linked.

The right side has the regular AUM Value (1.44+1.47+1.54)/3 months = 1.48 billion. This is where I'm getting 1.48 billion.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

This returns the expected results! Is there a way to do this without specifying ABCD etc? The real data has more advisors and the advisors can change so I wouldn't want to hardcode the names in the formula.

[Ashish_Mathur]

Good to hear that.  ABCD has nothing to do with your advisory names.  It is just a column heading.  There can be as many advisory in your data - nothing will need to be changed in my formula.

If my reply helped, please mark it as Answer.