Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@bkirshenbaum add following measure

 

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

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

@bkirshenbaum add following measure

 

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

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

That did the trick, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.