Calculation for multiple columns

[AntonKiselev]

Good day,

Could you please help me with the following issue:

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

[v-cherch-msft · Accepted solution]

Hi @AntonKiselev 

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,

[AntonKiselev]

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