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
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    efstel
    Icon for Helper I rankHelper I
    1 year ago

    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.