Forum Discussion

moromer's avatar
moromer
New Member
2 years ago
Solved

Matrix table and grouped columns data manipulation

My problem is to make a 'Total' column that collects data from every row of matrix table and calculates result according to some formula, for example: Col1/(Col4-Col2+Col3)*100

Difficulty is to extract grouped value within the row and manipulate it in the new column

         | Col1  | Col2  |   Col3 |   Col4 | Total 

row1 | 234   | 18      |   87    |   12     |  234/(12-18+87)

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi moromer ，

     

    According to your statement, I think your table should look like as below.

    As far as I know, you can try to transform your data and create a measure to show your data in a matrix visual.

    Firstly add an [Index] column and then unpiovt Col1/Col2/Col3/Col4 by [Index] in Power Query Editor.

    Measure:

    Measure = 
VAR _Col1 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col1")
VAR _Col2 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col2")
VAR _Col3 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col3")
VAR _Col4 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col4")
RETURN
IF(HASONEVALUE('Table'[Attribute]),SUM('Table'[Value]), DIVIDE(_Col1,_Col4-_Col2+_Col3)*100)

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi moromer ，

     

    According to your statement, I think your table should look like as below.

    As far as I know, you can try to transform your data and create a measure to show your data in a matrix visual.

    Firstly add an [Index] column and then unpiovt Col1/Col2/Col3/Col4 by [Index] in Power Query Editor.

    Measure:

    Measure = 
VAR _Col1 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col1")
VAR _Col2 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col2")
VAR _Col3 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col3")
VAR _Col4 = CALCULATE(SUM('Table'[Value]),'Table'[Attribute] = "Col4")
RETURN
IF(HASONEVALUE('Table'[Attribute]),SUM('Table'[Value]), DIVIDE(_Col1,_Col4-_Col2+_Col3)*100)

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.