Forum Discussion
Difference measure wrong, but only for 1 row
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?
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.
5 Replies
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.
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-
Hi Help_me ,Thank you for reaching out to the Microsoft fabric community forum.
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,
LakshmiHi Help_me ,
We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.
Best Regards,
Lakshmi.Hi Help_me ,
We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.
Best Regards,
Lakshmi.
