cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kennoe Regular Visitor
Regular Visitor

add amounts in one column conditional on other columns

I need to add together amounts in one column that are conditional on the two other columns. The simplified version of the table below illustrates what I need to do.

Status

Amount

Identifier

Planned

$100,000

1

Allocated

$100,000

1

Released

$100,000

1

Released

$15,000

1

Planned

$800,000

2

Allocated

$800,000

2

Released

$800,000

2

Planned

$1,000,000

2

Allocated

$1,000,000

2

Released

$1,000,000

2

Planned

$50,000

3

Allocated

$50,000

3

Released

$50,000

3

 

For each of the identifiers (1, 2 and 3), I want to add up the Amounts that correspond to the Status of “Released”. Using this table, the end result will be:

Identifier

Amount

 

1

$115,000

2

$1,800,000

3

$50,000

 

If additional amounts were added with a Released Status, the Amount column would need to be updated.

 

Thanks

Steve

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisMendoza Senior Member
Senior Member

Re: add amounts in one column conditional on other columns

@kennoe,

 

Measure = 
CALCULATE(SUM(YourTableName[Amount]),ALLEXCEPT(YourTableName,YourTableName[Identifier]),YourTableName[Status]="Released")

4.PNG

View solution in original post

3 REPLIES 3
ChrisMendoza Senior Member
Senior Member

Re: add amounts in one column conditional on other columns

@kennoe,

 

Measure = 
CALCULATE(SUM(YourTableName[Amount]),ALLEXCEPT(YourTableName,YourTableName[Identifier]),YourTableName[Status]="Released")

4.PNG

View solution in original post

kennoe Regular Visitor
Regular Visitor

Re: add amounts in one column conditional on other columns

Thanks Chris

 

When I apply this formula to my data, there is a mismatch between some, but not all of the amounts and the identifiers. (Using my example table, it would be like the amount for identifier 1 being aligned with identifier 2 and vice versa) What could be the potential reasons for this? The data in the actual tables used appear to be aligned appropriately.

Cheers

Steve

ChrisMendoza Senior Member
Senior Member

Re: add amounts in one column conditional on other columns

@kennoe,

 

Hard for me to tell, can you share more of your data?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 1,066 guests
Please welcome our newest community members: