Create Table from another table in PBI

[rui_mendes]

Hello everyone

I’m trying to create a table from another one like this:

(Table1)

Origin	Destination	Cost
A	B	1
B	E	2
C	A	3
D	D	4
A	C	5
B	A	6

And the end result would be:

(Table2)

Location	Origin	Destination	Cost =(Origin+Destination)/2
A	6	9	7.5
B	8	1	4.5
C	3	5	4
D	4	4	4
E	0	2	1

(Table2)[Location] – unique values from Origin and Destination

(Table2)[Origin] – sum of all cost with origin x (SUMIF in excel)

(Table2)[Destination] – sum of all cost with destination x (SUMIF in excel)

Thanks in advance.

[parry2k]

@rui_mendes it is super easy if you create another table with uniques value of locations and set the relationship of this new table with Origin and Destination columns, the new table will be on one side of the relationship and one relationship will be inactive, let's assume a relationship with destination column is inactive, now add the following measure

Sum Origin = SUM ( Table[Cost] )

Sum Destination = CALCULATE ( [Sum Origin], USERELATIONSHIP ( LocationTable[Lccation], Table[Destinatoin] ) ) 

on table visual, drop location from this new table, and above measures.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[rui_mendes]

Thanks for the answer.

The problem is that the initial table is constantly changing (at least once a week). Is it possible for the second table with unique values to be created automatically ?

[rui_mendes · Accepted solution]

Ok

Eventually i've figured it out (i think ....)

using this post:

https://community.powerbi.com/t5/Desktop/Append-Rows-using-Another-columns/td-p/401836

i came up with this:

Tabela1 = VAR tbl1 =

SELECTCOLUMNS ( Vendas, "Mun", [OriginCounty], "Desconto", [ValorDesconto]/2)

VAR tbl2 =

SELECTCOLUMNS ( Vendas, "Mun", [DestinationCounty], "Desconto", [ValorDesconto]/2)

RETURN

UNION ( tbl1, tbl2 )

which creates a new table (not with unique values), but when using a table view in PBI it aggregates the values, leaving the table as desired.