Power BI remove row breakdown for some group values

[Anonymous]

Hi,

I have blelow data, the value of Total = Inventory + Trade + Others.

What I want to see in the matrix is  to have only one line for Total, I don't want the breakdown of the Accounts for Total like below:

Is this possible?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a calculated column like

cate = SWITCH(TRUE(),[Group] in {"Inventory", "Trade", "Others"},"total")

Add matrix like:

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You can use a measure for the Account value.  Blank it out when the Group is "Total", fill it with the Account value if not.

Then use summation for the Amount.

[Anonymous]

@lbendlin , This might work, but I will loose the ability to drill down and up on the Account level.

[mahoneypat]

In your example, the desired matrix doesn't show account level for the Total row, so how will you drill down/up?  I think the best way would be to Filter Total from the matrix (visual level filter) and add a card right below or above the matrix that shows a measure for just Total.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat , there will be no value drill up and down for Total, but there will be for other Groups value.

I would like to have all the Groups in the matrix.

Thanks for the suggestion.

[lbendlin]

I don't think you mentioned that requirement.  Have you tried using a matrix visual where you put Group and Account in the rows, Amount in the value, and nothing in the columns?  Then expand all groups except the Total group.

[Anonymous]

I did not mention that  because I thought that is obvious, if you have any column in the row you would want to drill up and down.

Thanks for your matrix solution, I am aware of that solution, but I thought I would ask if there is another solution.