cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculation for multiple columns

Good day,

I need to get the sum depending on worker's name or ID.

I have the following data representation in the Data Warehouse:

 Date SwamperID SwamperName DriverID DriverName Qty 1/1/2019 2 Nate 1 John 100 1/1/2019 4 Nick 2 Nate 200 1/1/2019 1 John 3 Jack 300 2/1/2019 6 Den 5 Mike 400 2/1/2019 2 Nate 7 Doc 500

People can be only drivers, only swampers or both drivers and swampers.

Note:DriverID = Swamper ID

I need to sum up their activities from both roles.

for e.g.:

Nate Qty = 100+200+500 = 800 (he was a swamper on 2 occasions and a driver on 1 occasion)

Den Qty = 400 (he was a swamper on 1 occasion)

 NEEDED FORMAT Name Qty sum of all activities per Name Nate Nick John Den Jack Mike Doc

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Calculation for multiple columns

You may new a table like below and create a column to get the Qty sum.Attached sample file for your reference.

`Table = UNION(DISTINCT(Data[SwamperName]),DISTINCT(Data[DriverName]))`
```Column =
CALCULATE (
SUM ( Data[Qty] ),
FILTER (
Data,
Data[SwamperName] = 'Table'[SwamperName]
|| Data[DriverName] = 'Table'[SwamperName]
)
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

## Re: Calculation for multiple columns

You may new a table like below and create a column to get the Qty sum.Attached sample file for your reference.

`Table = UNION(DISTINCT(Data[SwamperName]),DISTINCT(Data[DriverName]))`
```Column =
CALCULATE (
SUM ( Data[Qty] ),
FILTER (
Data,
Data[SwamperName] = 'Table'[SwamperName]
|| Data[DriverName] = 'Table'[SwamperName]
)
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculation for multiple columns

Thank you very much for explaining and providing the example! It worked!

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)