Subtotal and totals not computing correctly

[efstel]

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'[ActualCost] table/column only when 'HeavyJob_CostSummary'[LMSEO] or 'JCCD'[LMSEO] has a value of "Labor" or "Material". This works with the following: 

NewColumn =

IF(

    [HJCost minus ActualCost] > 0,

    SUM('bihj HeavyJob_CostSummary'[HJCost]),

    SUM('JCCD'[ActualCost])    

)

or: 

NewMeasureCO =

VAR LMSEO_Value = SELECTEDVALUE('bihj HeavyJob_CostSummary'[LMSEO])

VAR HJCost = SUM('bihj HeavyJob_CostSummary'[HJCost])

VAR ActualCost = SUM('JCCD'[ActualCost])

RETURN

IF(

    LMSEO_Value IN {"Labor", "Material"} && HJCost > ActualCost,

    HJCost,

    ActualCost

)

However, when you subtotal or total all the values they are not correct. How do I get the subtotal and total to compute the correct value?

[v-yohua-msft]

Hi, @efstel 

Based on your information, I create sample tables:

Create a new measure and try the following DAX:

CorrectedMeasureCO = 
SUMX(
    'bihj HeavyJob_CostSummary',
    VAR LMSEO_Value = 'bihj HeavyJob_CostSummary'[LMSEO]
    VAR HJCost = 'bihj HeavyJob_CostSummary'[HJCost]
    VAR ActualCost = CALCULATE(SUM('JCCD'[ActualCost]), 'JCCD'[LMSEO] = LMSEO_Value)
    RETURN
    IF(
        LMSEO_Value IN {"Labor", "Material"} && HJCost > ActualCost,
        HJCost,
        ActualCost
    )
)

Also create relationship:

It compares two tables of data and takes the largest one. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[efstel]

That is not working correclty. Also, I can't create a relationship between the 'JCCD' table and the 'bihj HeavyJob_CostSummary' table other than a many to many. The tables come from two different data sources and have millions of lines of data.  However, there is the following relationship between the tables. 

JCJP is a dimension table and contains unique JobNoCostCode. 'JCCD' and 'bihj HeavyJob_CostSummary' are both fact tables. 

[Fowmy]

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

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."