Difference measure wrong, but only for 1 row

[Help_me]

Hello, I am using a measure to calulate "Goal Difference".

The measure DAX formula = SUM('TableX' [Goals By Region] - SUM('ActionsTable' [SalesOrderMade].

The Goals By Region is a static table shown in Chart 2 below. 

The ActionsTable is a dynamic cource I am pulling in from a dataserver, thus as more oders are made, we would get closer to the goals.

The proble is with the row labeled West becasue Achieved YTD - Goals should be a Goal Difference of 4,338 (not 18,838). Any tips on why the other rows are correct, but not this one?

Chart 1

Chart 2

[v-lgarikapat]

Hi @Help_me ,Thank you for reaching out to the Microsoft fabric community forum.

@Nasif_Azam , @Greg_Deckler 

Thanks for your prompt response

@Help_me I wanted to follow up and confirm whether you’ve had a chance to review the information  shared by @Nasif_Azam , @Greg_Deckler . If you have any questions or need further clarification, please don’t hesitate to reach out.

If you're still encountering any challenges, feel free to let us know we’d be glad to assist you further.

Looking forward to your response.

Best regards,
Lakshmi

[Nasif_Azam]

Hey  @Help_me ,

It looks like the calculation for “Goal Difference” in the West row is being thrown off because the measure is summing Goal - Achieved without correctly matching the West goal value to the West achievements. This usually happens when the region in the goals table does not perfectly match the region in the achievements table common causes are extra spaces, different spellings, or no active relationship between the two tables. Try the following approaches:

1. Create a proper Region dimension table

• Build a small Regions table with each region listed once.

• Link both your Goals table and your Achievements table to this Regions table.

• Clean the text fields (remove spaces and invisible characters) in Power Query to ensure “West” matches exactly in all tables.

2. Separate the measures instead of wrapping SUM around a direct subtraction:

Achieved YTD :=
SUM ( ActionsTable[SalesOrderMade] )

Goal :=
SUM ( TableX[Goals By Region] )

Goal Difference :=
[Goal] - [Achieved YTD]

3. If the relationship is inactive, activate it within the measure using:

Goal :=
CALCULATE (
    SUM ( TableX[Goals By Region] ),
    USERELATIONSHIP ( TableX[Region], Regions[Region] )
)

Once the region mapping and relationships are correct, West should display 9,500 – 5,162 = 4,338 instead of 18,838.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Greg_Deckler]

@Help_me Hard to say, could be that the relationship has an issue like "West " not matching "West" for example due to trailing space. The All Regions line is also off fo Goal Difference but not in an obvious way. Try having each SUM statement being a VAR and then return each separately to see where the problem is.