How to make matrix table with average only at the end

[gvlado]

Hello community.
Can someone help with some simple solution for the problem
Want to make matrix table and add total average at the end?

#	Product	Jan	Feb	March	Total	Average
1	A	10	20	30	60	20,0
2	B	5	10	20	35	11,7
3	C	25	27	10	62	20,7
6	Total	40	57	60	157	52,3

Thanks a lot

[v-yangliu-msft · Accepted solution]

Hi  @gvlado ,

I created some data:

Sorry, Power BI temporarily can not achieve this function, this is the Power BI design concept, no matter Column Total rows or Row Total rows are the last column or row of the matrix, but you can add a row in front of the Total, but you need to create a new table.

Here are the steps you can follow：

1. Create measure.

Table 2 =
var _table1=
SUMMARIZE(
    'Table','Table'[Product],
    "Month","Table_Average",
    "Value",
    AVERAGEX(
        FILTER(ALL('Table'),'Table'[Product]=EARLIER('Table'[Product])),[Value]))
return
UNION(
    'Table',_table1)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[gvlado]

This solution works in terms of Average but Total is now wrong (upgraded with this Average)
Original Total is 20+10+30= 60
New Total is 20+10+30+20=80

[Ritaf1983]

Hi @gvlado 

Unfortunately, we don't have an option for multiple totals on matrixes.

There is an idea about this issue, you can vote for it (i voted :))

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=29ec2808-fc5b-42a8-964a-b10d878654b0

You can also use a workaround with calculated groups, please refer to the link with the tutorial:

https://www.youtube.com/watch?v=ExCD0vM-OEU

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.