Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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?
In this image is the calculation you did vs AUM
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.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |