Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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,
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,
Thank you very much for explaining and providing the example! It worked!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |