Average Overdue days showing wrong values (Power BI)

[BalajiBuddha]

HI Everyone,

Can anyone help me why it is showing worng average total when i compare to excell with power bi, if calculation is wrong please correct me.

Average Overdue Days_Column =

    VAR A =

        IF(

            SALES_LEDGER_PAYMENTS_M3[Invoice Paid] IN {"No", "Partial"},

            SALES_LEDGER_PAYMENTS_M3[Overdue Days],

            IF(

                SALES_LEDGER_PAYMENTS_M3[Invoice Paid] = "Yes but Overdue",

                SALES_LEDGER_PAYMENTS_M3[Days between Due Date and Payment Date],

                BLANK()

            )

        )

    RETURN IF(ISBLANK(A), 0, A)

Based on above measure i am calculation Average like below

New Average OverdueDays =

CALCULATE(AVERAGEX(SALES_LEDGER_PAYMENTS_M3,SALES_LEDGER_PAYMENTS_M3[Average Overdue Days_Column]))

[v-prasare]

Hi @BalajiBuddha,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare · Accepted solution]

Hi @BalajiBuddha,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[cengizhanarslan]

Your Power BI “average total” is wrong because your logic is forcing blanks to 0, and AVERAGEX includes those zeros in the denominator. Use the below calculated column instead:

Average Overdue Days (Column) =
IF (
SALES_LEDGER_PAYMENTS_M3[Invoice Paid] IN { "No", "Partial" },
SALES_LEDGER_PAYMENTS_M3[Overdue Days],
IF (
SALES_LEDGER_PAYMENTS_M3[Invoice Paid] = "Yes but Overdue",
SALES_LEDGER_PAYMENTS_M3[Days between Due Date and Payment Date],
BLANK()
)
)

Then your average measure can simply be:

New Average OverdueDays =
AVERAGE ( SALES_LEDGER_PAYMENTS_M3[Average Overdue Days (Column)] )

[pankajnamekar25]

Hello @BalajiBuddha 

Try with measure approach

Average Overdue Days =
AVERAGEX (
SALES_LEDGER_PAYMENTS_M3,
SWITCH (
TRUE (),
SALES_LEDGER_PAYMENTS_M3[Invoice Paid] IN { "No", "Partial" },
SALES_LEDGER_PAYMENTS_M3[Overdue Days],
SALES_LEDGER_PAYMENTS_M3[Invoice Paid] = "Yes but Overdue",
SALES_LEDGER_PAYMENTS_M3[Days between Due Date and Payment Date],
BLANK ()
)
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn