Wrong Total sum

[Sarutra]

Hello,

I can't solve the total sum issue, can you help?

I am attaching a link to the Power BI file

https://sarutra-my.sharepoint.com/:u:/p/powerbi/EVlVgZt4wyRMrWefGdfwqEMBN8961ITPyhT4eOEXgFwsEA?e=kUM...

I will be very grateful for your help in solving the problem.

Arturas

[Ashish_Mathur]

Hi,

I created this measure

Measure = if(ISBLANK([Unpaid Amount]),BLANK(),SUMX(VALUES('sales-payments'[Indeksas]),[Unpaid Amount]))

Hope this helps.

[Sarutra]

Hi,

Rezult blank measure

Arturas

[Ashish_Mathur · Accepted solution]

You are welcome.  If my previous reply helped, please mark it as Answer.

[Ashish_Mathur]

In the screenshot, as you can see i get the correct result.

[Sarutra]

Sorry, you are right.

[Sarutra]

Do you have any ideas why the measure total is showing correctly, but the value in the row for some customers is incorrect?     

Arturas

[danextian]

Hi @Sarutra 

What do TRUE and NO TRUE mean? What is your expected result?

[rohit1991]

Hi @Sarutra ,

The "wrong total" issue in Power BI measures is really common and usually happens because DAX calculates the total differently than the individual rows. Here’s how I’d tackle it:

Use SUMX over VALUES: This ensures your custom calculation runs at the row level for each unique Invoice (or whatever your key field is), and then adds everything up for the total. For example:

Unpaid Amount = 
SUMX(
    VALUES('sales payments'[Invoice NR]),
    [Open Order Total] - [Payments]
)

Add HASONEVALUE logic if needed: If your calculation needs to handle both single rows and totals differently, you can use HASONEVALUE to switch logic, like this:

Unpaid Amount = 
IF(
    HASONEVALUE('sales payments'[Invoice NR]),
    [Open Order Total] - [Payments],
    SUMX(
        VALUES('sales payments'[Invoice NR]),
        [Open Order Total] - [Payments]
    )
)

Make sure your tables are joined correctly, and there aren’t any inactive relationships that could mess up the totals. Sometimes creating a quick table with SUMMARIZE or looking at a table visual can help spot why the totals are off. If it’s still not working, feel free to share a sample PBIX file with dummy data. Sometimes it’s something small that’s easy to spot with a real example.

[Sarutra]

It's not that simple. I am attaching the measure formula.

Unpaid Amount =

VAR BalanceToPay = [Open Order Total]  -- Paskutinė neapmokėta suma

VAR RunningTotal =

    CALCULATE(

        SUM('sales-payments'[Debet]),

        FILTER(

            ALLSELECTED('sales-payments'),

            'sales-payments'[Customer code] = SELECTEDVALUE('sales-payments'[Customer code]) &&

            'sales-payments'[Indeksas] >= MAX('sales-payments'[Indeksas])

        )

    )

VAR RemainingAmount =

    IF(

        BalanceToPay - RunningTotal >= 0,

        MAX('sales-payments'[Debet]),

        IF(

            BalanceToPay - (RunningTotal - MAX('sales-payments'[Debet])) > 0,

            BalanceToPay - (RunningTotal - MAX('sales-payments'[Debet])),

            0

        )

    )

RETURN

IF(

    HASONEVALUE('sales-payments'[Invoice NR]),

    IF(RemainingAmount > 0, RemainingAmount, BLANK()),

    SUMX(

        FILTER(

            ALLSELECTED('sales-payments'),

            'sales-payments'[Customer code] = SELECTEDVALUE('sales-payments'[Customer code])

        ),

        IF(

            BalanceToPay -

            CALCULATE(

                SUM('sales-payments'[Debet]),

                FILTER(

                    ALLSELECTED('sales-payments'),

                    'sales-payments'[Customer code] = SELECTEDVALUE('sales-payments'[Customer code]) &&

                    'sales-payments'[Indeksas] >= EARLIER('sales-payments'[Indeksas])

                )

            ) >= 0,

            'sales-payments'[Debet],

            IF(

                BalanceToPay -

                CALCULATE(

                    SUM('sales-payments'[Debet]),

                    FILTER(

                        ALLSELECTED('sales-payments'),

                        'sales-payments'[Customer code] = SELECTEDVALUE('sales-payments'[Customer code]) &&

                        'sales-payments'[Indeksas] >= EARLIER('sales-payments'[Indeksas])

                    )

                ) - 'sales-payments'[Debet] > 0,

                BalanceToPay -

                CALCULATE(

                    SUM('sales-payments'[Debet]),

                    FILTER(

                        ALLSELECTED('sales-payments'),

                        'sales-payments'[Customer code] = SELECTEDVALUE('sales-payments'[Customer code]) &&

                        'sales-payments'[Indeksas] <= EARLIER('sales-payments'[Indeksas])

                    )

                ),

                0

            )

        )

    )

)