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

[ashishd]

Hello Friends,

I have a simple query but struggling to get the same done in PowerBI.

I want to display the Row Total on the Rows after Items dimension. By default, we can have the left table but I have to show the raw numbers of Row Total and individual numbers as Percent of Total, similar to the one on the right.

I have created a Total DAX measure to get the Row Total but it being a measure, cannot be dragged onto the rows in a matrix. 
Any tips of achieving this would be helpful.

Sample pbix file is here: https://drive.google.com/file/d/1pn51DF2Egyx_EyFNZ3ICF9_jqiNVNw_6/view?usp=sharing

Thank you,
Ashish

[Anonymous]

Hi,@ashishd 
I am glad to help you.
According to your description, you want to have the date and total columns display different values separately, with the date column displaying the percentage and the total aggregation column displaying the total quantity
You need to customize the output in the matrix using the isincope function, which is commonly used in matrices.
Here are the results of my test, hope it helps you

Here is the DAX code.

C_% = 
VAR name_Item=[Item]
VAR num_Item=
CALCULATE(SUM(Sheet1[Metric]),FILTER(ALL('Sheet1'),'Sheet1'[Item]=name_Item)
)
RETURN DIVIDE(Sheet1[Metric],num_Item,0)

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

M_total = 
IF(ISINSCOPE(Sheet1[Date]),
FORMAT(SELECTEDVALUE(Sheet1[C_%]),"Percent"),[M_Item_Num])

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.

[ashishd]

Hi @Anonymous 

Thank you for your reply!

But this is not what I have requested for because the Total column is getting broken for each item per date in your output. I want the Dates in the columns like in the right image and the Total for all the dates for each item - (for A total is 60),  next to A.

If you closely notice the image and the two tables in it, you will see the difference is that Total column is the second after Items column and then for each date, there is the percent of total value listed.

Hope I was able to make it more clear. Please let me know if I am not clear.

Thank you again for your help!

[Anonymous · Accepted solution]

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.

[ashishd]

Thank you, @Anonymous!

This is really great. I understand now that we cannot have a DAX measure playing a role of a dimension in PowerBI and thus there is no easy way of putting a measure as a row.
But your approach has helped a lot in understanding how things can work in PowerBI.
Really appreciate your effort and time for helping me.

Regards,
Ashish

[Anonymous]

Hi,@ashishd 
You are welcome.
Your understanding is correct, there are very many limitations in the matrix of power BI, and many of the requirements need to be recreated with new data to realize them.