Creating a new row that gives me sum of all other rows in each column.

[jatin24]

Sounds like a very simple thing to do in Excel, but i just cant work it out in Power BI.

So my data is something like this - Consider various projects along with the  hours spent on different location for that project.

Project No	Location1	Location2	Location3	Location4
P0001	10	12	14	18
P0002	40	22	45	93
P0003	88	25	33	13
P0004	11	87	33	43

I need a new row inserted in the end that gives me a sum of all hours in each location.

Ultimately what i need is a bar chart that shows total hours - a bar for each location. 

Im thinking if i can get a row at the bottom that always calculates total for each column, i can transpose it and use it in the bar. 

Is there a better way to do this?

thank you

[ERD · Accepted solution]

@jatin24 , If I understood correctly your goal, here are the steps:

1. Unpivot location columns in Power Query

Table.UnpivotOtherColumns(#"Previous step", {"Project No"}, "Location", "Value")

2. Create a simple measure:

ValueAmt = SUM('Table'[Value])

3. Use the measure in the matrix / bar chart visual

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

[jatin24]

Perfect! Thanks so much.