Forum Discussion
I need help! Average.
Good morning
I'm a basic Power Bi user and I need help. I have an excel file with data and I have created in power BI an array with customers (rows) and months of the year (columns) with the values of the orders that they have placed throughout the year. I need that matrix to add a colunma at the end next to the "total" column where I average every month and by customer.
Attach an image of what the table is like:
Thanks a lot.
@Jose_Spain , You can make Total on average. But you can't add Average near the total.
There are workarounds (hybrid table)
if you're looking for a hybrid display with Matrix column and
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for the Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
3 Replies
- amitchandak
Super User
@Jose_Spain , You can make Total on average. But you can't add Average near the total.
There are workarounds (hybrid table)
if you're looking for a hybrid display with Matrix column and
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for the Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebcThank you very much for your help, but I see that the hybrid table is quite complicated to create and I do not know very well how to do it. Is there some other simpler way?
Hi Jose_Spain ,
Sample data is this.
DateValueCustomer
Wednesday, January 1, 2020 1 A Sunday, February 2, 2020 2 A Tuesday, March 3, 2020 3 A Saturday, April 4, 2020 4 A Tuesday, May 5, 2020 5 A Saturday, June 6, 2020 6 A Tuesday, July 7, 2020 7 A Saturday, August 8, 2020 8 A Wednesday, September 9, 2020 9 A Saturday, October 10, 2020 4 A Wednesday, November 11, 2020 6 A Saturday, December 12, 2020 4 A Tuesday, January 1, 2019 1 A Saturday, February 2, 2019 1 A Wednesday, January 1, 2020 1 B Sunday, February 2, 2020 2 B Tuesday, March 3, 2020 3 B Saturday, April 4, 2020 1 B Tuesday, May 5, 2020 5 B Saturday, June 6, 2020 4 B Tuesday, July 7, 2020 7 B Saturday, August 8, 2020 8 B Wednesday, September 9, 2020 9 B Saturday, October 10, 2020 6 B Wednesday, November 11, 2020 1 B Saturday, December 12, 2020 2 B Tuesday, January 1, 2019 3 B Thursday, April 4, 2019 5 B 1.Create a calculated column.
Month = FORMAT([Date],"MMMM")2.Create a new table by entering data. The Index column is to sort the Month column.
3.Create a measure.
Measure = IF(MAX('Month'[Month])in VALUES('Table'[Month]),CALCULATE(SUM('Table'[Value]),FILTER('Table',[Month]=MAX('Month'[Month]))),IF(MAX('Month'[Month])="Average",AVERAGE('Table'[Value])))4.The result is this, you can filter by year.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
