Totals are wrong in Aggregation

[Pfoster]

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
            )
        )
)

[v-sdhruv]

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

[v-sdhruv]

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

[v-sdhruv · Accepted solution]

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!

[Shahid12523]

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.

[Pfoster]

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)
)

[FBergamaschi]

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

[Greg_Deckler]

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

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