Forum Discussion
Totals are wrong in Aggregation
- 11 months 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.
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)
)