Forum Discussion
Finding the sum from two columns on the same table
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 |
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.
7 Replies
ryan_mayuSuper User
Anonymous
in PQ， select both ID and shipments columns and unpivot other columns
then you can get what you want
- v-easonf-msft
Community Support
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. - 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
- ryan_mayu
Anonymous
what's the expected result based on your sample data?
