Forum Discussion

SCNCKS1's avatar
SCNCKS1
Helper I
3 years ago
Solved

Creating a calculated column from 2 tables

I am hoping someone can help me.

I am trying to create a calculated column in TBL_PROD that sums the totals in the amount column from TBL_CT based on the date and project number that will then add it to the TBL_PROD so it only shows the total value regardless of how many date entries are in TBL_PROD.  The cost tracking total variable below works, but it adds the same value (total of the amount column for the day) under each date. My thought was to divide by the total number of entries for the date, but the count rows formula is counting everything on the date, not just the entries for the project so my number is way too small. There is no relationship between the tables.

Project Cost =
VAR COST_TRACKING_TOTAL =
SUMX(
    FILTER(TBL_CT,
    TBL_CT[EffectiveDate] = TBL_PROD[WorkDate] && TBL_CT[ProjectIDId] = TBL_PROD[ProjectIDId] ) , TBL_CT[Amount])
VAR COUNT_COST_TRACKING = CALCULATE(COUNTROWS(TBL_PROD),FILTER(TBL_PROD,TBL_PROD[WorkDate] =  EARLIER(TBL_PROD[WorkDate])))
RETURN
COST_TRACKING_TOTAL

Original Data

EffectiveDateAmount
10/28/2022$45.93
10/28/2022$67.77
10/28/2022$240.00
10/28/2022$250.00
10/28/2022$1,360.00
10/28/2022$560.00
10/28/2022$100.00
10/28/2022$450.00
11/8/2022$520.00
11/8/2022$1,150.00
11/8/2022$17,337.54
11/8/2022$17,134.91
11/8/2022$236.25
11/8/2022$327.61
12/1/2022$354.60
12/1/2022$44.00
12/2/2022$105.68
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    Please provide sanitized sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Please provide sanitized sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.