topic Totals are wrong in Aggregation in Desktop

Totals are wrong in Aggregation

Pfoster — Mon, 01 Sep 2025 14:29:06 GMT

Hello,
I have created some Measures to calculate FX-, Price-, and Volume Effects for sales. FX_Effects working well and also the totals in aggregation are correct.
Coming now to my Problems:

Price Effect = VAR CompScenarioSel = SELECTEDVALUE('Comparison Selector'[Compared to]) VAR FocusScenario = SELECTEDVALUE(FocusScenario[Focus Scenario]) VAR FocusYear = SELECTEDVALUE(GemeinsameJahre[Year]) VAR ReportCurrency = SELECTEDVALUE('Currency Selector'[Currency], "EUR") RETURN SUMX( ADDCOLUMNS( SUMMARIZE( 'Sales Data', 'Sales Data'[Company Code], 'Sales Data'[Material], 'Sales Data'[Posting period], 'Sales Data'[Inv Currency] ), "NSPFocus", CALCULATE( [NSPFocus_LC], 'Sales Data'[Scenario] = FocusScenario, 'Sales Data'[Year] = FocusYear ), "NSPComp", CALCULATE( [NSPComp_LC], 'Sales Data'[Scenario] = SWITCH( CompScenarioSel, "Budget", "Budget", "Prior Year", FocusScenario ), 'Sales Data'[Year] = SWITCH( CompScenarioSel, "Budget", FocusYear, "Prior Year", FocusYear - 1 ) ), "FX", [FX_Rate_CompPeriod], "FocusMT", [FocusMT] ), IF( [Company Code] IN { "PF3091","PF3HGS","PF3262","PF3267" } || [Material] = "100222" || [NSPComp] <= 0 || [NSPFocus] <= 0, 0, ([NSPFocus] - [NSPComp]) * [FX] * [FocusMT] * 1000 ) )

this one is my Price effect. If I am on lowest level (Customer-Article) line, the effect is calculated correctly. Even, when I am selecting multiple month.
But looking at the grand totals, there is an error, and it seems, also on total line, the effect (and also the volume effect) is calculated, and not only the sum of the single lines.
How do I have to adjust my Measures, that the totals are working well, when I start to aggregate the single lines.

Volume Effect = SUMX ( SUMMARIZE( 'Sales Data', 'Sales Data'[Company Code], 'Sales Data'[Material], 'Sales Data'[Posting period], 'Sales Data'[Inv Currency] ), VAR CompCode = [Company Code] VAR Mat = [Material] VAR CompMT = CALCULATE([CompMT], 'Sales Data'[Company Code] = CompCode, 'Sales Data'[Material] = Mat ) VAR FocusMT = CALCULATE([FocusMT], 'Sales Data'[Company Code] = CompCode, 'Sales Data'[Material] = Mat ) VAR NSPFocus = CALCULATE([NSPFocus_LC], 'Sales Data'[Company Code] = CompCode, 'Sales Data'[Material] = Mat ) VAR NSPComp = CALCULATE([NSPComp_LC], 'Sales Data'[Company Code] = CompCode, 'Sales Data'[Material] = Mat ) VAR FX_Focus = CALCULATE([FX_Rate_FocusPeriod], 'Sales Data'[Company Code] = CompCode, 'Sales Data'[Material] = Mat ) VAR FX_Comp = CALCULATE([FX_Rate_CompPeriod], 'Sales Data'[Company Code] = CompCode, 'Sales Data'[Material] = Mat ) RETURN IF ( CompCode IN { "PF3091" } || FocusMT = 0, 0, IF ( CompMT = 0, (FocusMT - CompMT) * NSPFocus * FX_Focus * 1000, (FocusMT - CompMT) * NSPComp * FX_Comp * 1000 ) ) )

Re: Totals are wrong in Aggregation

Greg_Deckler — Mon, 01 Sep 2025 14:36:54 GMT

Pfoster First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p/4475982

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Totals are wrong in Aggregation

FBergamaschi — Mon, 01 Sep 2025 15:17:53 GMT

Ciao Pfoster,

at the lower level, calculations are going by customer or article and at that level of detail your DAX works correctly, you say

Therefore, we must force the code to work always customer by customer or article by article (or by tuples article - customer)

Now, you say that one of your measures works perfectly at all levels, can you show that measure code?

So I can inspect it

Anyway, just to give you an idea, the SUMMARIZE will have to change probably and the RETURN statement

Of course if you could please show data samples, pictures of the error, model layout and arrangements of the visual wjere you see the bad total, that would help helping you

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Totals are wrong in Aggregation

Shahid12523 — Tue, 02 Sep 2025 03:47:11 GMT

Your totals are wrong because SUMX(SUMMARIZE(...)) recalculates logic at the total level instead of summing row-level results.
✅ Fix:
- Create a base measure that calculates the effect per row.
- Wrap it in a second measure that uses SUMX to aggregate those row-level results.
This ensures correct totals by summing pre-calculated values instead of re-evaluating logic in total context.

Re: Totals are wrong in Aggregation

Pfoster — Tue, 02 Sep 2025 11:36:35 GMT

Sorry, I do not get it. I have tried to build up different Measures, but none works.

This is my Measure, where I got the right row-results:

Price Effect = IF([FocusMT]>0 && [CompMT]>0, ([NSPFocus_LC] - [NSPComp_LC]) * [FX_Rate_CompPeriod] * [FocusMT] * 1000,BLANK())

These are my further Measures:

NSPComp_LC = DIVIDE([CompNS],[CompMT],0)/1000

NSPFocus_LC = DIVIDE([FocusNS],[FocusMT],0)/1000

FocusNS = VAR FocusScenarioSel = SELECTEDVALUE(FocusScenario[Focus Scenario]) VAR FocusYearSel = SELECTEDVALUE(GemeinsameJahre[Year]) RETURN CALCULATE( SUM('Sales Data'[Net Sales]), 'Sales Data'[Scenario] = FocusScenarioSel, 'Sales Data'[Year] = FocusYearSel )

CompNS = VAR CompScenarioSel = SELECTEDVALUE('Comparison Selector'[Compared to]) VAR FocusScenario = SELECTEDVALUE(FocusScenario[Focus Scenario]) VAR FocusYear = SELECTEDVALUE(GemeinsameJahre[Year]) VAR CompScenario = SWITCH( CompScenarioSel, "Budget", "Budget", "Prior Year", FocusScenario, BLANK() ) VAR CompYear = SWITCH( CompScenarioSel, "Budget", FocusYear, "Prior Year", FocusYear - 1, BLANK() ) RETURN CALCULATE( SUM('Sales Data'[Net Sales]), 'Sales Data'[Scenario] = CompScenario, 'Sales Data'[Year] = CompYear )

CompMT = VAR CompScenarioSel = SELECTEDVALUE('Comparison Selector'[Compared to]) VAR FocusScenario = SELECTEDVALUE(FocusScenario[Focus Scenario]) VAR FocusYear = SELECTEDVALUE(GemeinsameJahre[Year]) VAR TargetScenario = SWITCH( CompScenarioSel, "Budget", "Budget", "Prior Year", FocusScenario ) VAR TargetYear = SWITCH( CompScenarioSel, "Budget", FocusYear, "Prior Year", FocusYear - 1 ) RETURN CALCULATE( SUM('Sales Data'[Sales Reported]), 'Sales Data'[Scenario] = TargetScenario, 'Sales Data'[Year] = TargetYear )

FocusMT = CALCULATE( SUM('Sales Data'[Sales Reported]), TREATAS(VALUES(FocusScenario[Focus Scenario]), 'Sales Data'[Scenario]), TREATAS(VALUES(GemeinsameJahre[Year]), 'Sales Data'[Year]) )

FX_Rate_CompPeriod = AVERAGEX( FILTER( 'Sales Data', 'Sales Data'[Scenario] = SELECTEDVALUE(FocusScenario[Focus Scenario]) && 'Sales Data'[Year] = SELECTEDVALUE(GemeinsameJahre[Year]) ), VAR InvoicingCurrency = 'Sales Data'[Inv Currency] VAR CompMonth = 'Sales Data'[Posting period] // Vergleichsszenario & -jahr bestimmen VAR CompScenarioSel = SELECTEDVALUE('Comparison Selector'[Compared to]) VAR FocusScenario = SELECTEDVALUE(FocusScenario[Focus Scenario]) VAR FocusYear = SELECTEDVALUE(GemeinsameJahre[Year]) VAR CompScenario = SWITCH( CompScenarioSel, "Budget", "Budget", "Prior Year", FocusScenario ) VAR CompYear = SWITCH( CompScenarioSel, "Budget", FocusYear, "Prior Year", FocusYear - 1 ) VAR ReportCurrency = SELECTEDVALUE('Currency Selector'[Currency], "EUR") VAR Period = CompMonth & CompYear & CompScenario // FX Invoicing → USD VAR FX_InvToUSD = LOOKUPVALUE( 'Exchange Rates Master'[average FX Rate], 'Exchange Rates Master'[USD vs.], InvoicingCurrency, 'Exchange Rates Master'[PeriodYearScen], Period ) // FX USD → Reporting Currency VAR FX_USDToReport = LOOKUPVALUE( 'Exchange Rates Master'[average FX Rate], 'Exchange Rates Master'[USD vs.], ReportCurrency, 'Exchange Rates Master'[PeriodYearScen], Period ) RETURN DIVIDE(FX_InvToUSD, FX_USDToReport) )

Re: Totals are wrong in Aggregation

v-sdhruv — Thu, 04 Sep 2025 12:02:21 GMT

Hi Pfoster ,

Make sure filters and relationships between tables are set up correctly, as these can affect totals.
You can refer a similar thread here- Solved: Wrong or incorrect total sums / problem with aggre... - Microsoft Fabric Community

Hope this helps!

Re: Totals are wrong in Aggregation

v-sdhruv — Tue, 09 Sep 2025 12:22:17 GMT

Hi Pfoster ,

Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank you for using Microsoft Community Forum

Re: Totals are wrong in Aggregation

v-sdhruv — Sat, 13 Sep 2025 12:52:17 GMT

Hi Pfoster ,
I hope the explaination provided, has addressed your query.
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out.

Thank you