Forum Discussion
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!
Anonymous add following measure
Hard and Soft Cost = CALCULATE( SUM( Table8[Approved Budget] ), Table8[Cost Category] IN {"Hard Cost", "Soft Cost" } )
8 Replies
- parry2k
Super User
Anonymous 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
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!
- parry2k
Anonymous so then there can be more than 1 soft and hard cost, let's say both costs can be each month, correct?