cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AntonKiselev Frequent Visitor
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

Accepted Solutions
Microsoft v-cherch-msft
Microsoft

Re: Calculation for multiple columns

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
Microsoft v-cherch-msft
Microsoft

Re: Calculation for multiple columns

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

AntonKiselev Frequent Visitor
Frequent Visitor

Re: Calculation for multiple columns

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)