Calculating sum of costs by project and activity

[tberr86]

Hello community,

Working on a schedule of costs report that will show budgeted cost of each activity but am beyond my DAX experience. One table (ScheduleCosts) holds the grouping of costs by activity. One table (HomeBudget) holds the homes budgeted cost. One table (HomeSchedule) holds the date of completion for each activity. Sample data below, example of intended result in CALCULATED AMOUNT column. I think I need to create a Calculated table that filters the costs to the selected record's activity, then SUMs the budgeted amount filtered by Home and costs in the filtered calculated table. 

I appreciate the assistance!

[Anonymous · Accepted solution]

Hi @tberr86 ,

You can create a measure as below to get it, please find the details in the attachment.

Calculated Amount = 
VAR _devhouse =
    SELECTEDVALUE ( 'HomeSchedule'[Devhouse] )
VAR _activitycode =
    SELECTEDVALUE ( 'HomeSchedule'[activitycode] )
VAR _costcodes =
    CALCULATETABLE (
        VALUES ( ScheduleCosts[Cost-Code] ),
        FILTER ( 'ScheduleCosts', 'ScheduleCosts'[activitycode] = _activitycode )
    )
VAR _amount =
    CALCULATE (
        SUM ( 'HomeBudget'[budgetamount] ),
        FILTER (
            'HomeBudget',
            'HomeBudget'[DEVHOUSE] = _devhouse
                && 'HomeBudget'[Cost-Code] IN _costcodes
        )
    )
RETURN
    _amount

Best Regards

[tberr86]

Thank you for the support on this!