Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AntonKiselev
Frequent Visitor

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:

DateSwamperIDSwamperNameDriverIDDriverNameQty
1/1/20192Nate1John100
1/1/20194Nick2Nate200
1/1/20191John3Jack300
2/1/20196Den5Mike400
2/1/20192Nate7Doc500

 

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 
NameQty sum of all activities per Name
Nate 
Nick 
John 
Den 
Jack 
Mike 
Doc 

 

 

 

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

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,

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.

View solution in original post

2 REPLIES 2
v-cherch-msft
Employee
Employee

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,

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.