cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate Sum of Two Rows Based on Category

Hello!

Using the data below, I am trying to create a measure to add Soft Cost and Hard Cost for each project based upon the most recent modified date. So, for Project MLI, I'd get a total of \$84,741,150.

Project       Cost Category                                             Approved Budget              Modified                  Created

 MLI Soft Cost \$          11,906,904.00 5/15/2019 10:34 5/14/2019 10:27 MLI Hard Cost \$          56,799,598.00 5/15/2019 10:34 5/14/2019 10:27 MLI Land \$             5,817,821.00 5/15/2019 10:34 5/14/2019 10:27 MLI Retail \$                365,563.00 5/15/2019 10:34 5/14/2019 10:27 MLI Soft Cost Contingency \$                829,679.00 5/15/2019 10:34 5/14/2019 10:27 MLI Marketing \$             1,005,250.00 5/15/2019 10:34 5/14/2019 10:27 MLI FF & E \$             2,537,756.00 5/15/2019 10:34 5/14/2019 10:27 MLI Finance/Closing + Interest Reserve \$             2,638,608.00 5/15/2019 10:34 5/14/2019 10:27 MLI Hard Cost Contingency \$             2,839,980.00 5/15/2019 10:34 5/14/2019 10:27 WLR Land \$             6,487,000.00 5/15/2019 10:35 5/14/2019 10:46 WLR Hard Cost \$          65,045,904.00 5/15/2019 10:35 5/14/2019 10:46 WLR Soft Cost \$             7,123,236.00 5/15/2019 10:35 5/14/2019 10:46 WLR Retail \$             1,097,887.00 5/15/2019 10:35 5/14/2019 10:46 WLR Soft Cost Contingency \$                668,395.00 5/15/2019 10:35 5/14/2019 10:46 WLR Marketing \$                875,000.00 5/15/2019 10:35 5/14/2019 10:46 WLR Finance/Closing + Interest Reserve \$             2,453,367.00 5/15/2019 10:35 5/14/2019 10:46 WLR FF & E \$             3,086,132.00 5/15/2019 10:35 5/14/2019 10:46 WLR Hard Cost Contingency \$             2,505,705.00 5/14/2019 10:46 5/14/2019 10:46 EIL Finance/Closing + Interest Reserve \$             2,008,478.00 5/15/2019 10:36 5/14/2019 11:26 EIL Hard Cost \$          58,497,196.00 5/15/2019 10:36 5/14/2019 11:26 EIL Marketing \$                750,000.00 5/15/2019 10:36 5/14/2019 11:26 EIL Soft Cost \$             8,392,237.00 5/15/2019 10:36 5/14/2019 11:26 EIL Land \$             8,657,946.00 5/15/2019 10:36 5/14/2019 11:26 EIL Hard Cost Contingency \$             1,887,895.00 5/15/2019 10:36 5/14/2019 11:27 EIL Soft Cost Contingency \$                576,308.00 5/15/2019 10:36 5/14/2019 11:27 EIL FF & E \$             1,425,436.00 5/15/2019 10:36 5/14/2019 11:27 EIL Retail \$                450,000.00 5/15/2019 10:36 5/14/2019 11:27

Any help would be greatly appreciated.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate Sum of Two Rows Based on Category

`Hard and Soft Cost = CALCULATE( SUM( Table8[Approved Budget] ), Table8[Cost Category] IN {"Hard Cost", "Soft Cost" } )`

Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.

8 REPLIES 8
Super User

## Re: Calculate Sum of Two Rows Based on Category

@bkirshenbaum can there be more than one soft and hard cost for same project? how you get to the total of  \$84,741,150. for MLI

Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Calculate Sum of Two Rows Based on Category

Apologies for the confusion, it should be ‭68,706,502‬ for MLI.

There would only be one soft and hard cost for the project, however this will be party of a report that is updated monthly and those costs will change accordingly. So, when filtering monthly, it would need to add Hard & Soft cost by month.

Does that help clarify?

Thank you!

Super User

## Re: Calculate Sum of Two Rows Based on Category

@bkirshenbaum so then there can be more than 1 soft and hard cost, let's say both costs can be each month, correct?

Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Calculate Sum of Two Rows Based on Category

Correct. In any given month, there would only be 1 hard cost and 1 soft cost, but each subsequent month will have its own hard and soft cost.

Super User

## Re: Calculate Sum of Two Rows Based on Category

@bkirshenbaum how you are planning to view this cost

Project | Month | Hard and Soft Cost (in this case it will show whatever hard and soft cost is in the month)

or

Project | Hard and Soft Cost (in this case show the cost from the most recent month)

Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Calculate Sum of Two Rows Based on Category

I would view it as Project | Month| (Soft Cost + Hard Cost) |

Super User

## Re: Calculate Sum of Two Rows Based on Category

`Hard and Soft Cost = CALCULATE( SUM( Table8[Approved Budget] ), Table8[Cost Category] IN {"Hard Cost", "Soft Cost" } )`

Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Calculate Sum of Two Rows Based on Category

That did the trick, thank you!