DAX Measure Changes When I add a field to my table visualization

[Auto_queen]

I've tried a few different things and I'm stuck. I think it might have to do with how my tables are related, but not sure. 

I have this measure:  

TEST GP = IF(
[Total Credit] <> [Total Adjust Fee Amount],

[Collected GP],

SUM('Gross Profit'[TR1__GP_Amount__c]))

The values shown in the table are correct when the "Name" field is added to my table. But when I remove that field, it matches the Colelcted GP measure.  My goal is to show this measure monthly, but even when I add the month name to the table it also shows the 44K instead of the 42K. 

Here are my other measures:

Total Credit = SUMX(Collections, Collections[Credit]))

Total Adjust Fee Amount = SUMX('Closing Report', 'Closing Report'[TR1__Adjusted_Fee_Amount__c]))

Collected GP = 

IF(HASONEFILTER('Closing Report'[BH_ID__c]),[Total Credit] * [Closing Report Split],SUMX(VALUES('Closing Report'[BH_ID__c]),[Total Credit]*[Closing Report Split])))

 Closing Report Split = IF(HASONEFILTER(Collections[Num]),CALCULATE(SUMX('Gross Profit','Gross Profit'[Closing_Report_Split__c]),'Gross Profit'[TR1__GP_Amount__c] > 0),SUMX(VALUES(Collections[Num]),CALCULATE(SUMX('Gross Profit','Gross Profit'[Closing_Report_Split__c]),'Gross Profit'[TR1__GP_Amount__c] > 0)))

[tamerj1]

Hi @Auto_queen 

please try

TEST GP =
SUMX (
    VALUES ( 'Table'[Name] ),
    IF (
        [Total Credit] <> [Total Adjust Fee Amount],
        [Collected GP],
        CALCULATE ( SUM ( 'Gross Profit'[TR1__GP_Amount__c] ) )
    )
)

[Auto_queen]

Sorry I should have said I tried that already and it did not work. It gave me a larger number 705917.60

[tamerj1]

@Auto_queen 

Check twice. Did you wrap SUM with CALCULATE?

[Auto_queen]

Yes. 

TEST GP = SUMX(

    VALUES('Closing Report'[Name]),

     IF(

         [Total Credit] <> [Total Adjust Fee Amount],

[Collected GP],

CALCULATE(SUM('Gross Profit'[TR1__GP_Amount__c]))))

If I don't put CALCULATE before the SUM it is an even larger number. 

I think it might be how my tables are related... 

[tamerj1]

@Auto_queen 

How are they related?

[Auto_queen]

Collections table > Invoice Table (Many to One) 

Collections is where I get the Total Credit measure. 

Invoice Table > Closing Report Table (Many to One)

Closing report table is where I get the Total Adjust Fee Amount measure. 

Gross Profit Table > Closing Report Table (Many to One)

Gross Profit table is where I get the closing report split measure and the total gp measure. 

They are all active relationships. 

[tamerj1]

@Auto_queen 
There is no issue with the model. The 'Closing Report' table is dimention table that is filtering other tables. I think the problem is somehow comming from the HASONEFILTER conditions in the sub-measures.

[Auto_queen]

Actually, looking at my table again, I think the error is with my "Total Adjust Fee Amount" measure. 

When I have the "Name" column in the table that measure matches my total credit measure. When I take the "Name" column out the total adjust fee amount changes to 1,683,651. So, TEST GP formula is working because the total credit <> total adjust fee amount. 

I just tried this formula change but it didn't work. Any suggestions? I appreciate your help through this. 

Total Adjust Fee Amount =
SUMX(
VALUES('Closing Report'[Name]),
CALCULATE(
SUMX('Closing Report','Closing Report'[TR1__Adjusted_Fee_Amount__c])
)
)

[Auto_queen]

Okay let me play around with those. I know there was a reason why I added that in there, but maybe the SUMX(VALUES)  is all I need...