I can’t get the correct sum Total of the measurements

[oggis]

Hi,

I need help calculating the aging balance based on overdue days.  
You need to break "PartialUnpaidBalance"    down the aging report by days overdue (

Dienos_po_Due Tot)

(for example, from 0 to –15 days).
No matter how much I try, I can’t get the correct Total sum, even though the rows look fine. Do you have any experience on how to solve this?

I’d be grateful for your help.

Aging balance TEST.pbix

Arturas

[FBergamaschi · Accepted solution]

The solution is subsitute your VALUES ( 'Invoice'[Invoice No] ) with the right granularity

PartialUnpaidBalance_SuTotal =

IF(

    ISINSCOPE( Invoices[Invoice No] ),

    // --- Logika detalės eilutei ---

    // Pirmiausia patikriname sąlygą konkrečiai eilutei

    IF(

        [Dienos_po_Due Tot] = -25,

        // Jei sąlyga teisinga, vykdome originalią logiką

        VAR showFlag_detail =

            CALCULATE(

                IF( [Balance] < 0, [Invoice Amount] )

                + [CombinedUnpaidOrBalance]

            )

        VAR curBal_detail   = CALCULATE( [Balance] )

        VAR invAmt_detail   = CALCULATE( [Invoice Amount] )

        RETURN

            IF(

                showFlag_detail > 0,

                ABS( curBal_detail ),

                IF( curBal_detail < 0, invAmt_detail, BLANK() )

            ),

        // Jei sąlyga eilutei neteisinga, nieko nerodome

        BLANK()

    ),

    // --- Logika TOTAL eilutei ---

    // Naudojame SUMX, kad iteruotume per sąskaitas ir pritaikytume sąlygą kiekvienai atskirai

    SUMX(

        SUMMARIZE( Invoices, 'Calendar'[Date], Invoices[Invoice No], 'Customer Master'[Customer ID] ),

        // Kiekvienai sąskaitai patikriname sąlygą

        IF(

            CALCULATE([Dienos_po_Due Tot]) = -25,

            // Jei sąlyga sąskaitai teisinga, atliekame originalų skaičiavimą

            VAR showFlag_total =

                CALCULATE(

                    IF( [Balance] < 0, [Invoice Amount] )

                    + [CombinedUnpaidOrBalance]

                )

            VAR curBal_total = CALCULATE( [Balance] )

            VAR invAmt_total = CALCULATE( [Invoice Amount] )

            RETURN

                IF(

                    showFlag_total > 0,

                    ABS( curBal_total ),

                    IF( curBal_total < 0, invAmt_total, 0 )

                ),

            // Priešingu atveju, ši sąskaita prie bendros sumos neprisideda (grąžiname 0)

            0

        )

    )

)

You get the right result now

Please give kudos / mark as solution if this solved

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

I am checking the pbix to help you

Can you please show me some image of where I should check and what rresult you should get?

[oggis]

[FBergamaschi · Accepted solution]

The solution is subsitute your VALUES ( 'Invoice'[Invoice No] ) with the right granularity

PartialUnpaidBalance_SuTotal =

IF(

    ISINSCOPE( Invoices[Invoice No] ),

    // --- Logika detalės eilutei ---

    // Pirmiausia patikriname sąlygą konkrečiai eilutei

    IF(

        [Dienos_po_Due Tot] = -25,

        // Jei sąlyga teisinga, vykdome originalią logiką

        VAR showFlag_detail =

            CALCULATE(

                IF( [Balance] < 0, [Invoice Amount] )

                + [CombinedUnpaidOrBalance]

            )

        VAR curBal_detail   = CALCULATE( [Balance] )

        VAR invAmt_detail   = CALCULATE( [Invoice Amount] )

        RETURN

            IF(

                showFlag_detail > 0,

                ABS( curBal_detail ),

                IF( curBal_detail < 0, invAmt_detail, BLANK() )

            ),

        // Jei sąlyga eilutei neteisinga, nieko nerodome

        BLANK()

    ),

    // --- Logika TOTAL eilutei ---

    // Naudojame SUMX, kad iteruotume per sąskaitas ir pritaikytume sąlygą kiekvienai atskirai

    SUMX(

        SUMMARIZE( Invoices, 'Calendar'[Date], Invoices[Invoice No], 'Customer Master'[Customer ID] ),

        // Kiekvienai sąskaitai patikriname sąlygą

        IF(

            CALCULATE([Dienos_po_Due Tot]) = -25,

            // Jei sąlyga sąskaitai teisinga, atliekame originalų skaičiavimą

            VAR showFlag_total =

                CALCULATE(

                    IF( [Balance] < 0, [Invoice Amount] )

                    + [CombinedUnpaidOrBalance]

                )

            VAR curBal_total = CALCULATE( [Balance] )

            VAR invAmt_total = CALCULATE( [Invoice Amount] )

            RETURN

                IF(

                    showFlag_total > 0,

                    ABS( curBal_total ),

                    IF( curBal_total < 0, invAmt_total, 0 )

                ),

            // Priešingu atveju, ši sąskaita prie bendros sumos neprisideda (grąžiname 0)

            0

        )

    )

)

You get the right result now

Please give kudos / mark as solution if this solved

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[oggis]

Thank you very much for the solution.

[FBergamaschi · Accepted solution]

You are welcome

I forgot to clean a bit your measure, there are many not needed CALCULATE statements and the entire first part is performed by the second part, so here is the cleaned up version (much shorter!)

PartialUnpaidBalance_SuTotal =

SUMX(

    SUMMARIZE( Invoices, 'Calendar'[Date], Invoices[Invoice No], 'Customer Master'[Customer ID] ),

    IF(

        [Dienos_po_Due Tot] = -25,

        VAR invAmt_total = [Invoice Amount]

        VAR curBal_total = [Balance]

        VAR showFlag_total = IF( curBal_total < 0, invAmt_total ) + [CombinedUnpaidOrBalance]

        RETURN

            IF(

                showFlag_total > 0,

                ABS( curBal_total ),

                IF( curBal_total < 0, invAmt_total )

            )

    )

)

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[oggis]

Hi,

if I add an additional condition  [Dienos_po_Due Tot] >= -15 && [Dienos_po_Due Tot] <= 0

And the dataset contains a million rows and about 3,000 customer IDs, and I’m getting an ‘out of resources’ error. How can this be resolved?

PartialUnpaidBalance_SuTotalNEW =

SUMX(

    SUMMARIZE( Invoices, 'Calendar'[Date], Invoices[Invoice No], 'Customer Master'[Customer ID] ),

    IF(

        [Dienos_po_Due Tot] >= -15 && [Dienos_po_Due Tot] <= 0,

        VAR invAmt_total = [Invoice Amount]

        VAR curBal_total = [Balance]

        VAR showFlag_total = IF( curBal_total < 0, invAmt_total ) + [CombinedUnpaidOrBalance]

        RETURN

            IF(

                showFlag_total > 0,

                ABS( curBal_total ),

                IF( curBal_total < 0, invAmt_total )

            )

    )

)

[FBergamaschi]

Create a Variable like this

PartialUnpaidBalance_SuTotalNEW =

SUMX(

    SUMMARIZE( Invoices, 'Calendar'[Date], Invoices[Invoice No], 'Customer Master'[Customer ID] ),
VAR Dienos = [Dienos_po_Due Tot]
RETURN

    IF(

      Dienos >= -15 && Dienos <= 0,

        VAR invAmt_total = [Invoice Amount]

        VAR curBal_total = [Balance]

        VAR showFlag_total = IF( curBal_total < 0, invAmt_total ) + [CombinedUnpaidOrBalance]

        RETURN

            IF(

                showFlag_total > 0,

                ABS( curBal_total ),

                IF( curBal_total < 0, invAmt_total )

            )

    )

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[DataNinja777]

Hi @oggis ,

The issue with incorrect totals is common in DAX and happens because a measure that works on a single row doesn't understand the context of the "Total" row, which contains many different values. To fix this, you need to use a pattern that correctly iterates through the invoices before summing them up.

First, you'll need to create a disconnected table that defines your aging periods. In the Modeling tab, select New Table and use the following DAX expression. This table will hold the definitions for your buckets and should not have any relationships with other tables in your model.

AgingBuckets =
DATATABLE (
    "Aging Bucket", STRING,
    "Min Days", INTEGER,
    "Max Days", INTEGER,
    "Sort Order", INTEGER,
    {
        { "Current", 1, 99999, 1 },
        { "0-15 Days Overdue", -15, 0, 2 },
        { "16-30 Days Overdue", -30, -16, 3 },
        { "31-60 Days Overdue", -60, -31, 4 },
        { "Over 60 Days Overdue", -99999, -61, 5 }
    }
)

Next, create the core measure that will calculate the balance for each aging bucket. Right-click on your Invoices table and select New measure, then enter this formula. This measure will work correctly for both individual rows and the grand total.

Aged Balance =
CALCULATE (
    SUM ( 'Invoices'[PartialUnpaidBalance] ),
    FILTER (
        'Invoices',
        VAR vDaysOverdue = 'Invoices'[Dienos_po_Due Tot]
        VAR vMinDays = MIN ( 'AgingBuckets'[Min Days] )
        VAR vMaxDays = MAX ( 'AgingBuckets'[Max Days] )
        RETURN
            vDaysOverdue >= vMinDays && vDaysOverdue <= vMaxDays
    )
)

This DAX works because the FILTER function iterates through every row of the Invoices table. It checks if an invoice's overdue days (Dienos_po_Due Tot) fall within the Min and Max days for the bucket being displayed in your visual. CALCULATE then sums the PartialUnpaidBalance only for the invoices that meet the criteria. This row-by-row evaluation before summing is the key to getting the totals right.

Finally, build a Matrix visual. Place Customer Master[Customer] on the Rows, AgingBuckets[Aging Bucket] on the Columns, and your new [Aged Balance] measure in the Values. For correct column ordering, select the Aging Bucket column in the Data pane, go to Column tools, and Sort by column using the Sort Order column.

Best regards,

[oggis]

Thank you for your help, but I’m still not getting the correct result; it may be because both [PartialUnpaidBalance] and [Dienos_po_Due Tot] are measures.

[Greg_Deckler]

@oggis 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

[zainqazi96]

Have you tried to get all the sums in static forms like creating an summarized table using dax or power query

[oggis]

The static form doesn’t suit me; it has to be measure