How can I add values in a Matrix? Please help

[Anonymous]

Hello,

I'm trying to add my values in my Table/Matrix on PowerBI and wanted to know if there was a way to do that without editing the source data. 

In the simplest terms, I'm trying to add column 15 to column 3 and I want that SUM to replace the value in column 3. I also want column 15 to become 0. I'm very limited on what I can share regarding the data but this data is shared by a field called "Hours". The fields crossed in red are the various departments within the company producing these hours. Columns 15 and 3 should not be separate, they should be summed together.

Is it possible to create some measure that would allow me to do this? Thanks for the help.

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Base data:

Solution1:

Use the following dax to create a new column:

Column_new3 = SUM('Table'[Column15])

Column_15new = 0

Then get the below:

Fianl delete before column and rename new column.

Solution 2:

Use the following dax to create a new table：

Table 2 =
SELECTCOLUMNS (
    'Table',
    "Column1", 'Table'[Column1],
    "Column2", 'Table'[Column2],
    "Column3", SUM ( 'Table'[Column15] ),
    "Column15", 0
)

Final get:

And then could delete before table and rename new table.

Wish it is helpful for you!

Best Regards

Lucien

[AllisonKennedy]

@Anonymous  we need to know a bit more about the data model. Can you share the configuration of the matrix and is there only 1 row for [Hours] field? 

Using the FILTER or CALCULATE functions here could help. Alternatively you may be able create a New Group on the department column (Right click in the fields pane on Department field > New Group). If you are able, this might be better done in Power Query.