Take field and append to bottom of table

[Gusd8]

Thank you for looking. I am not even sure how to text describe what I want to do. Imagine you own three houses and you have listed their values.

City	State	Value
Minneapolis	MN	180,000
Saint Paul	MN	190,000
Madison	WI	210,000

So the sum or the value of homes is 580,000. You expect the houses to increase 5% in value in the coming 5 years. So you add a column and multiple Value by .05

City	State	Value	Profit
Minneapolis	MN	180,000	9,000
Saint Paul	MN	190,000	9,500
Madison	WI	210,000	10,500

So the reporting goal is to have a matrix table that has state as the main Group...and then to be able to expand and see city value and profit value...so the data would look like this...

City	State	Value
Minneapolis	MN	180,000
Saint Paul	MN	190,000
Madison	WI	210,000
Profit	MN	18,500
Profit	WI	10,500

Right now I I having a brain cramp, I have no idea on how I would bring the profit column down and append to the City column.

Please don't judge the example, it is not a real world example, it meant to show what I have been asked to do with a different data set (and what they are asking actually does make sense). Any ideas on how to do this efficiently would be greatly appreciated. The only thing I can think of right now is build a duplicate data set, add the calculated column, change the name of all of the city values to Profit and then append the second table to the first. It just feels like a lot of work and processing. Thank you again for looking. All ideas are welcome.

[v-stephen-msft]

Hi @Gusd8 ,

It's easy to append query in Power Query, please refer to

Append queries - Power Query | Microsoft Learn

Here's an example about appending in Power Query. I created Table (2) and appended Table (2) to Table.

If you don't want to append in Power Query, you can try calculated tables using DAX in Power BI Desktop.

Table 2 = UNION(SELECTCOLUMNS(ADDCOLUMNS('Table (3)',"Profit",[Value]*0.5),"City","Profit","State",[State],"Value",[Profit]),'Table (3)')

Best Regards,

Stephen Tao

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

[Gusd8]

I should also say that the third table could easily look like this...

City	State	Value
Minneapolis	MN	180,000
Saint Paul	MN	190,000
Madison	WI	210,000
Profit	MN	9,000
Profit	MN	9,500
Profit	WI	10,500

It would get grouped in the Matrix 🙂 Thank You