DAX Measure Totals Not Matching Sum of Rows — SUMX with Row Logic Still Misbehaving

[Giorgi1989]

Hi everyone,

I’m running into a recurring issue in Power BI where a measure that works perfectly at the row level gives incorrect totals — the total doesn't match the sum of the individual rows. I’ve already tried the usual SUMX(VALUES(...), ...), HASONEVALUE pattern etc., but the total still doesn’t align with the visible row-level results.

Here’s the context:

• I’m calculating a potential uplift potential value based on margin differences. 

• The logic includes a condition that compares [CM%] of material to both a customer-level average and country / product group level average CM%. If CM% of material is lower than both of the above, then the uplift potential is realisable.  In other words, price increases are warranted to bring the margin levels up. 

• The row-level values are correct, but the total does not add up. 

I’ll share the full DAX and some screenshots/examples below. I’d love to hear how others have tackled this — especially if you’ve found reliable patterns for making totals behave like a true sum of rows when using conditional logic.

Unique ID	Customer	Material	Print type	Technical Group	Country	Sales	Increment	Uplift potential
1000	A	Dummy1	R	MP	USA	218.9K	6,32 %	13 828
1001	B	Dummy2	R	MP	Colombia	145.2K	2,16 %	3 143
TOTALS						364.1K		37 340

Uplift Potential =
CALCULATE(
IF(
HASONEVALUE('DataTable'[UniqueID]),
[Sales] * [Increment],
SUMX(
VALUES('DataTable'[UniqueID]),
[Sales] * [Increment]
)
)
)

Increment

Calculate(

    ABS([CM%]-[Unweighted CM% avg]      - -  - Here the absolute difference between the CM% and unveighted average of country                                                                              level and product level CM% is calculated.

    )

)

Please, also note that there are 10 or page level filters applied. 

Any ideas or comments would be greatly appreciated. I have wasted hours trying to figure out what the hell is happening; but debugging has not yielded any tangible results. 

Thanks in advance!

[Giorgi1989]

A further update:

The measure works correctly when I replace [Increment] with a hardcoded value (e.g., 2). This indicates that the issue likely originates within the Increment measure.

Uplift Potential =
CALCULATE(
IF(
HASONEVALUE('DataTable'[UniqueID]),
[Sales] * [Increment],
SUMX(
VALUES('DataTable'[UniqueID]),
[Sales] * [Increment]
)
)
)

Increment

Calculate(

    ABS([CM%]-[Unweighted CM% avg]    

    )

)

CM% = 

CM% =

        Calculate(

            Divide(

                sum('DataTable'[Contribution margin]),

                sum('DataTable'[Net sales]))

Unweighted CM% avg =

    Divide(

        [Ø-CM% Tech.Gr + Country]+[Ø-CM% Tech.Gr],2)   --  Unweighted average of the product group and country-level CM%, combined with the product group’s global-level CM%.

As I continue to debug, any ideas would be welcome. 

[Praful_Potphode]

Hi @Giorgi1989 

try below:

Uplift Potential = 
SUMX(
    'DataTable',
    'DataTable'[Sales]*'DataTable'[Increment]
)

i have tried to fix the total problem, let me know about the other issue in terms of input/output snapshots.

Sample PBIX

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Giorgi1989]

Thank you for the response. I have tried your approach: Now row-level calculations are wrong, but it indeed sums up the rows correctly:

Unique ID	Customer	Material	Print type	Technical Group	Country	Sales	Increment	Uplift potential
1000	A	Dummy1	R	MP	USA	218.9K	6,32 %	19 444
1001	B	Dummy2	R	MP	Colombia	145.2K	2,16 %	209
TOTALS						364.1K		19 653

[GeraldGEmerick]

@Giorgi1989 Is there any way to provide a sample data to recreate this problem? These types of issues are devilishly difficult to debug without actually being able to test things out. Can we assume that Sales is a simple SUM? Often, you really want to use ADDCOLUMNS in conjunction with SUMMARIZE or SUMMARIZECOLUMNS to aggregate your data exactly as it appears in the visual and the SUMX over that. The other thing that you can try is a Visual Calculation as this can make calculations like this easier.

[Giorgi1989]

LATEST UPDATE - The problem resolved. Thank you all for your support!

[v-priyankata]

Hi @Giorgi1989 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@GeraldGEmerick @Praful_Potphode Thanks for the inputs.
Glad that you found the solution. Please accept the helpful response from users as the accepted solution.
Thanks.

[v-priyankata]

Hi @Giorgi1989 

Thank you for reaching out to the Microsoft Fabric Forum Community.

Glad that you found the solution. Please accept the helpful response from users as the accepted solution.
Thanks.

[Giorgi1989]

Thank you for your response, Gerald.

The dataset contains sensitive margin information for several global players, so I’m unable to share the PBIX file as is. I’ve experimented with functions like SUMMARIZE and ADDCOLUMNS to address the issue.

For reference, the Sales measure is a straightforward sum, as shown below:

Sales = SUM('DataTable'[Sales])