Forum Discussion
Calculation for multiple columns
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 |
Hi Anonymous
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,
2 Replies
Hi Anonymous
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,