Sum Based on if a Value is available in different Table

Question

I am trying to produce a % variance between 2 costs.

Cost 1) "CTS Cost" - this is the theoretical cost, what i believe it should have cost.

Cost 2) "CHILT Cost" - this is the actual cost, how much i am being billed for.

The "CTS Cost" is a prediction model based on business operations. This prediction model runs when Orders/Sales are input.

The "CHILT Cost" is an invoice stating actual costs and comes in the weekly report which comes through on every Monday for the previous weeks deliveries.

I am trying to calculate sum of CTS - Sum of CHILT and visualize the variance between the two.

This is my current measure -

Total_Cost_All_CTS = IF([Total_Cost_All_CHILT] = 0, BLANK(),[Total_Cost_Picking_CTS] + [Total_Cost_RHD_CTS] + [Total_Cost_Storage_CTS])

I only want to sum the sales from CTS if the value in CHILT is available.

Reason being that CTS model will give us figures for a sale that has been ordered but not delivered. Once it is delivered, the CHILT data is recieved.

There could be a few weeks between receiving this data and the sales being run through the CTS model.

This causes a Skew in my Variance as it is including data for CTS costs when we have either not yet received the data or may have a missing week.

Both the CTS and CHILT are in different tables with a relationship to a master date table.

I am not trying to match the data by sales, only by date.

For example,

Week 10 2019, CTS Cost - £10, CHILT Cost - £8, Inlcude in CTS total

Week 11 2019, CTS Cost - £10, CHILT Cost - No Data Recieved, Dont Inlcude In CTS Total

Week 12 2019, CTS Cost - £30, CHILT Cost - £20, Inlcude in CTS total

Week 13 2019, CTS Cost - £50, CHILT Cost - Data Due Next week, Dont Inlcude In CTS Total

Total CTS Cost for 2019 = £40

Total CHILT cost for 2019 = £28

Variance of £12

Currently my formula would say the total CTS Cost = £100 which then gives me a variance of £88.

The measure i have above works on when using WeekCommencing, however i need to do a total variance for the year, but exclude the weeks in the CTS total where there is no data or we have no received it yet so i can get a true variance between costs.

Please let me know if anymore information is required.

Thanks

LT

EDIT

I require the output to be a measure that is the SUM of CTS Costs where there was also a cost for CHILT during that week.

So based off of the example, i require a measure to hold the equation that gives me this result "Total CTS Cost for 2019 = £40"

Answer 1

Create a measure like:

Variance = CALCULATE(SUM(Cost[CTS Cost]) - SUM(Cost[CHILT Cost]),FILTER(Cost,not ISBLANK(Cost[CHILT Cost])))

Result:

When you only want the rows where Chilt Cost is not blank you can add a filter:

Result: