Dax Query Incorrect Total

[GeorgWildmoser]

Hi,

my DAX query is producing the correct values by line but the incorrect value for the total. 

I thought I could fix the total problem with sumx. But obviously I'm doing something wrong here.

The DAX query:

Netto (12M v.DocDate) =

// Define the maximum date in 'TimeIfDocumentElsePostingDate' table

var maxDate = MAX('TimeIfDocumentElsePostingDate'[Date Date])

// Get the current date minus one day

var aktDate = TODAY() - 1

// Determine which date to use: maxDate or the current date (aktDate)

var useDate = IF(maxDate <= aktDate, maxDate, aktDate)

// Create a table of dates within the past year from 'useDate'

var DateTbl = DATESINPERIOD('TimeIfDocumentElsePostingDate'[Date Date], useDate, -1, YEAR)

// Calculate the Intermediate List for the last 12 months (IL12M)

var IL12M =

    CALCULATETABLE(

        ADDCOLUMNS(

            // Summarize data from 'CUB_AccountsReceivable_Tax' table by certain dimensions and dates

            SUMMARIZE(

                CUB_AR,

                DIM_Dim5[Dimension5Display],

                DIM_VoucherAR[VoucherARBusinessKeySurrogate],

                TimeCloseDate[Date Date],

                TimeDueDate[Date Date]

            ),

            // Add a column for due date plus 90 days

            "@DueDatePlus90", DATEADD(TimeDueDate[Date Date], 90, DAY),

            // Add a column for the current close date, handling blanks and dates beyond 'useDate'

            "@CloseDateCur", IF(

                ISBLANK(TimeCloseDate[Date Date]) || TimeCloseDate[Date Date] > useDate,

                useDate,

                TimeCloseDate[Date Date]

            )

        ),

        DateTbl,

        // Filter where 'TimeDueDate'[TimeBusinessKeySurrogate] is not blank

        NOT ISBLANK('TimeDueDate'[TimeBusinessKeySurrogate]),

        // Filter by specific document types in 'DIM_VoucherAR'[VoucherARDocumentTypeShort]

        DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}

    )

// Summarize the filtered table by VoucherARBusinessKeySurrogate

var VOD =

    SUMMARIZE(

        IL12M,

        DIM_VoucherAR[VoucherARBusinessKeySurrogate]

    )

// Return the sum of 'TaxBaseAmountCur' from the filtered table IL12M, applying an additional filter for specific document types

RETURN

    CALCULATE(

    SUMX(

        VOD,

        [TaxBaseAmountCur]

    ),

    DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}

)

Please give me some support. Thanks a lot.

[xifeng_L · Accepted solution]

Hi @GeorgWildmoser 

There is a general method to solve the problem that the total does not equal the breakdown, you can refer to it.

Create New Measure = 
SUMX(
    CROSSJOIN(
        VALUES('Table'[Row Field1]),
        VALUES('Table'[Row Field2]),
        VALUES('Table'[Row Field3]),
        ...
    ),
    [Old Measure]
)

According to your screenshot, you should fill the first 6 columns into VALUES in order.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~