Forum Discussion

efstel's avatar
efstel
Helper 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'[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?
 
DAX
measure

4 Replies

  • Fowmy's avatar
    Fowmy
    Super 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
      Helper 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."

  • 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
      Helper 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.