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 ,

Fix for Incorrect Totals in Power BI

1. Adjust Measure Logic: Use SUMX to calculate row-by-row: Unpaid Amount = SUMX(sales-payments, sales-payments[Open Order Total] - sales-payments[Credit])

2. Handle Totals Separately: Use ISINSCOPE to distinguish rows from totals: Unpaid Amount =
   IF(
   ISINSCOPE(sales-payments[Index]),
   sales-payments[Open Order Total] - sales-payments[Credit],
   SUM(sales-payments[Open Order Total]) - SUM(sales-payments[Credit])
   )
3. Check Data Model: Ensure relationships are correct and no inactive ones affect the calculation. This should resolve your issue.

[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

            )

        )

    )

)