Finding the sum from two columns on the same table

[Anonymous]

Hello, I am new to power bi but I am having hard time finding the sum of two columns on the same table. Example: I want to find the total shipments going to each state. Thank You for your help.

Table1

Id	From	To	Shipments
1	New York	Texas	52
2	CA	New York	15
3	Texas	CA	32
4	WI	MN	58
5	MN	Texas	40

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Try formula as below:
calculated table:

Table = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "State", Table1[From] ),
        SELECTCOLUMNS ( Table1, "State", Table1[To] )
    )
)

calculated column:

Total Shipments = 
CALCULATE (
    SUM ( Table1[Shipments] ),
    FILTER ( 'Table1', 'Table1'[From] = 'Table'[State] )
)
    + CALCULATE (
        SUM ( Table1[Shipments] ),
        FILTER ( 'Table1', 'Table1'[To] = 'Table'[State] )
    )

The result will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank You So much!!!

[ryan_mayu]

@Anonymous 

in PQ， select both ID and shipments columns and unpivot other columns

then you can get what you want

[ryan_mayu]

@Anonymous 

what's the expected result based on your sample data?

[Anonymous]

it would be total shipment would be = sum of shipment( From + To)

State	Shipment
New York	67
CA	47
Texas	124
MN	98
WI	58

[Anonymous]

thanks for the reply, when I try to create the Location table using that formula(location = distinct(distinct(Table[from]),distinct(Table[to]))), i am getting error

[amitchandak]

@Anonymous , to what brings from and to in same column and show the sum ?

if yes you need to create a table 

location = distinct(distinct(Table[from]),distinct(Table[to]))

join with both to and from, one will be inactive assume to 

then create a measure 

measure = sum(Table[Shipments])  + CALCULATE(sum(Table[Shipments]), userelationship(table[to], location [from]))

if needed refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi