Forum Discussion

efstel's avatar
efstel
Icon for Helper I rankHelper I
1 year ago

Subtotal and totals not computing correctly

I have two tables and want to use the values in the table/column 'JCCD'[ActualCost] except when in the values in table/column 'bihj HeavyJob_CostSummary'[HJCost] are higher than in the 'JCCD'[ActualC...
DAX
measure
Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
1 year ago

efstel 

You need to interate over the table, this will get the sub total right:

NewMeasureCO =
SUMX(
    'JCCD',
    VAR LMSEO_Value = LOOKUPVALUE('bihj HeavyJob_CostSummary'[LMSEO], 'bihj HeavyJob_CostSummary'[KeyColumn], 'JCCD'[KeyColumn])
    VAR HJCost = LOOKUPVALUE('bihj HeavyJob_CostSummary'[HJCost], 'bihj HeavyJob_CostSummary'[KeyColumn], 'JCCD'[KeyColumn])
    VAR ActualCost = 'JCCD'[ActualCost]
    
    RETURN
    IF(
        LMSEO_Value IN {"Labor", "Material"} && HJCost > ActualCost,
        HJCost,
        ActualCost
    )
)
  • efstel's avatar
    efstel
    Icon for Helper I rankHelper I
    1 year ago

    I'm not 100% sure what "KeyColumn" to use in the two tables. I've tried what I thougth was correct, but I keep getting an "Error fetching data" message that states "A table of multiple values was supplied where a sigle value was expected."