Forum Discussion
Helper IMatrix Help. Avg instead of Total
Hi,
I have created a matrix with Max values. I need to display the avg of these values instead of the Max value in the Totals field. I know I can create the matrix using avg figures, but that is not what I am trying to acheive.
Any help would be greatly appreciated.
Hi dmoggie ,
Try the following formula:
Measure = IF( HASONEVALUE('Table'[Date]), MAX('Table'[Value]), AVERAGE('Table'[Value]) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
v-kkf-msftCommunity Support
Hi dmoggie ,
Try the following formula:
Measure = IF( HASONEVALUE('Table'[Date]), MAX('Table'[Value]), AVERAGE('Table'[Value]) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dmoggieHi Winniz,
Thank you for your time.
This is closer to what I am trying to acheive, although not quite correct. The values in the matrix are max values and the total now displays avg values, but not the avg of the data contained within the matrix.
For example at 11:00:00 I would expect the avg to be 75.63.
I have a seperate calendar table and for the above page I filter by day of week, could this be causing an issue? When amending your code I seems to always get the highest value in the totals row.
Again any further help would be greatly appreciated.
- v-kkf-msft
Hi dmoggie ,
Try the following formula:
Measure = var tab = FILTER( 'Table', 'Table'[Date] in ALLSELECTED('Calendar'[Date]) ) return IF( HASONEVALUE('Table'[Date]), MAXX(tab,'Table'[Value]), AVERAGEX(tab,'Table'[Value]) )This is my PBIX file.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- dmoggie
Good Afternoon Winniz,
Thank you so much for your help. Happy to confirm this is just what I required to progress my report.
- PhilipTreacy
Super User
Hi dmoggie
Have you written a measure to get the MAX ? If so then rewrite to use AVERAGE e.g.
Average 11:00 = AVERAGEX ( 'Table','Table'[11:00:00])If your matrix has values in the Values wells, chnage the aggregation to Average
Regards
Phil
- dmoggie
Hi Phil,
Thank you for taking time out to answer my query. unfortunately this is not realy what I am after.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file.
- PhilipTreacy
Please supply an example of what you actually want. My solution shows the average in the total. Maybe you meant something different.
