Creating total values for different departments

[Anonymous]

Hello eveyone,

I need your help. 

I have a matrix smth like below:

But I want it to have as below:

A and B are different departments and I want to include "Total" in the same matrix or table. Is it possible? 

[Anonymous · Accepted solution]

HI @Anonymous,

AFAIK, the default matrix features did not support these. (these values will aggregate on grand total level instead of split into different groups based on their categories)
I'd like to suggest you create an unconnected table with all categories you wanted. Then you can use this as the 'row' of the matrix and write the measure formula as the 'value' of matrix to lookup values from the raw table based on the current category.

SELECTEDVALUE function - DAX | Microsoft Docs

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup.com)

Regards,

Xiaoxin Sheng

[Whitewater100]

Hi:

Normally the data should go in:

Dept  No.

A       2

A      4

B     5

B     1

You could duplicate this table in Power Query and change the Department name to "Total" and then append it to the first table and then you can obtain this result.

[Whitewater100]

Hi:

I didn't see the first reply and just want to mention when you are in Power Query with this original table there is a Fill feature where you can fill down your missing Department name on those two data points. Then proceed with the suggestion

[tamerj1]

@Anonymous 
It sounds crazy, but you can union/append the original table with a copy that has the value "total" instead of the project names then use the new table in your visual. You can do it in either power query or using dax

[Anonymous]

Hi @tamerj1 , I have it already using union all in SQL view (dataset) and it has many disadvantages in terms of user experience, but thanks.