Forum Discussion
Totals are wrong in Aggregation
- 1 year ago
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!
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.
- Pfoster1 year agoResolver I
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)/1000NSPFocus_LC = DIVIDE([FocusNS],[FocusMT],0)/1000FocusNS = 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) )