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

MLISoft Cost $          11,906,904.005/15/2019 10:345/14/2019 10:27
MLIHard Cost $          56,799,598.005/15/2019 10:345/14/2019 10:27
MLILand $             5,817,821.005/15/2019 10:345/14/2019 10:27
MLIRetail $                365,563.005/15/2019 10:345/14/2019 10:27
MLISoft Cost Contingency $                829,679.005/15/2019 10:345/14/2019 10:27
MLIMarketing $             1,005,250.005/15/2019 10:345/14/2019 10:27
MLIFF & E $             2,537,756.005/15/2019 10:345/14/2019 10:27
MLIFinance/Closing + Interest Reserve $             2,638,608.005/15/2019 10:345/14/2019 10:27
MLIHard Cost Contingency $             2,839,980.005/15/2019 10:345/14/2019 10:27
WLRLand $             6,487,000.005/15/2019 10:355/14/2019 10:46
WLRHard Cost $          65,045,904.005/15/2019 10:355/14/2019 10:46
WLRSoft Cost $             7,123,236.005/15/2019 10:355/14/2019 10:46
WLRRetail $             1,097,887.005/15/2019 10:355/14/2019 10:46
WLRSoft Cost Contingency $                668,395.005/15/2019 10:355/14/2019 10:46
WLRMarketing $                875,000.005/15/2019 10:355/14/2019 10:46
WLRFinance/Closing + Interest Reserve $             2,453,367.005/15/2019 10:355/14/2019 10:46
WLRFF & E $             3,086,132.005/15/2019 10:355/14/2019 10:46
WLRHard Cost Contingency $             2,505,705.005/14/2019 10:465/14/2019 10:46
EILFinance/Closing + Interest Reserve $             2,008,478.005/15/2019 10:365/14/2019 11:26
EILHard Cost $          58,497,196.005/15/2019 10:365/14/2019 11:26
EILMarketing $                750,000.005/15/2019 10:365/14/2019 11:26
EILSoft Cost $             8,392,237.005/15/2019 10:365/14/2019 11:26
EILLand $             8,657,946.005/15/2019 10:365/14/2019 11:26
EILHard Cost Contingency $             1,887,895.005/15/2019 10:365/14/2019 11:27
EILSoft Cost Contingency $                576,308.005/15/2019 10:365/14/2019 11:27
EILFF & E $             1,425,436.005/15/2019 10:365/14/2019 11:27
EILRetail $                450,000.005/15/2019 10:365/14/2019 11:27

 

Any help would be greatly appreciated.

 

Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate Sum of Two Rows Based on Category

@bkirshenbaum add following measure

 

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

image.png





Did I answer your question? Mark my post as a solution!


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
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





Did I answer your question? Mark my post as a solution!


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




 




bkirshenbaum Frequent Visitor
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
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?





Did I answer your question? Mark my post as a solution!


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




 




bkirshenbaum Frequent Visitor
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
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)





Did I answer your question? Mark my post as a solution!


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




 




bkirshenbaum Frequent Visitor
Frequent Visitor

Re: Calculate Sum of Two Rows Based on Category

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

Super User
Super User

Re: Calculate Sum of Two Rows Based on Category

@bkirshenbaum add following measure

 

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

image.png





Did I answer your question? Mark my post as a solution!


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




 




bkirshenbaum Frequent Visitor
Frequent Visitor

Re: Calculate Sum of Two Rows Based on Category

That did the trick, thank you!