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

Average data Quarterly by Category

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Average data Quarterly by Category

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Super User IV
Super User IV

Re: Average data Quarterly by Category

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jhendrsn Frequent Visitor
Frequent Visitor

Re: Average data Quarterly by Category

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

 

 

Super User IV
Super User IV

Re: Average data Quarterly by Category

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jhendrsn Frequent Visitor
Frequent Visitor

Re: Average data Quarterly by Category

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.

Super User IV
Super User IV

Re: Average data Quarterly by Category

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jhendrsn Frequent Visitor
Frequent Visitor

Re: Average data Quarterly by Category

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.

Super User IV
Super User IV

Re: Average data Quarterly by Category

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
jhendrsn Frequent Visitor
Frequent Visitor

Re: Average data Quarterly by Category

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.

Super User IV
Super User IV

Re: Average data Quarterly by Category

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors