How to show a Row Total as Row element in a matrix?

Hi,ashishd 

Thank you for your reply.

I'm sorry that I didn't give you a satisfactory solution before.

According to your request, you want to display the "Total" column in the second column of the matrix, without destroying the total column of the matrix.

First of all, I need to point out that the matrix visual in power BI itself has very many limitations, which is why you said it is very difficult to realize in power BI, it is generally not recommended to display different columns of data side by side in a matrix.

The rows and columns in the matrix must be real columns in the data (whether they are columns of the original data or a human-created calculate column), the data you provided does not have a real total column, so you must create a new table to indirectly realize your needs: next to [item] (the total column as a real column, which will be displayed next to the [item])

You can do this by customizing the matrix (note that this method only works when there is less data, because you need to recreate a new table based on the original data).
Here are my test results:

Need to re-create a new table based on the Columns displayed in the matrix [Header]

the table data sort by [_index] column

 

 

M_result = 
 VAR result=SWITCH(TRUE(),
    SELECTEDVALUE(Header[category])="Materials",
    SWITCH(TRUE(),
        SELECTEDVALUE(Header[level])="5/1/2024",FORMAT([M_A],"Percent"),
        SELECTEDVALUE(Header[level])="5/2/2024",FORMAT([M_A],"Percent"),
        SELECTEDVALUE(Header[level])="5/3/2024",FORMAT([M_A],"Percent"),
        SELECTEDVALUE(Header[level])="5/4/2024",FORMAT([M_A],"Percent")
    ),
    SELECTEDVALUE(Header[category])="others",
    SWITCH(TRUE(),
        SELECTEDVALUE(Header[level])="Total",FORMAT([M_Item_Num],0)
    )
 ) 

 

 

 

RETURN result

M_A = 
VAR _selected =SELECTEDVALUE(Header[level])
VAR metric_value=CALCULATE(SUM('Sheet1'[Metric]),FORMAT('Sheet1'[Date],"m/d/yyyy")=_selected)
VAR sum_Metric=
CALCULATE(SUM(Sheet1[Metric]),FILTER(ALL('Sheet1'),'Sheet1'[Item]=MAX('Sheet1'[Item]))
)
RETURN 
DIVIDE(metric_value,sum_Metric,0)

 

 

 

M_Item_Num = CALCULATE(
    SUM('Sheet1'[Metric]),FILTER(ALL(Sheet1),Sheet1[Item]=MAX('Sheet1'[Item]))
    )

 

 

This will do what you want, but you can't use the original total value (the matrix's own total value is empty, because the Total column and the other date columns are no longer part of the same column when aggregated by rows, and even if they are aggregated, they won't be able to compute the correct answer). Luckily, we've already created a custom [total] column to replace it

Moving the “Total” value to an arbitrary position in the matrix (sorted by setting the _Index column) can be achieved by sorting the column values in the matrix and discussing them and artificially controlling the output (by performing different Measures)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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