Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How can I add values in a Matrix? Please help

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.

 

Need Help
Show and Tell
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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