Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Thank You So much!!!
@Anonymous
in PQ, select both ID and shipments columns and unpivot other columns
then you can get what you want
Proud to be a Super User!
@Anonymous
what's the expected result based on your sample data?
Proud to be a Super User!
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 |
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |