Forum Discussion

BalajiBuddha's avatar
BalajiBuddha
Frequent Visitor
7 months ago
Solved

Average Overdue days showing wrong values (Power BI)

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]))



 

 

Need Help
  • v-prasare's avatar
    v-prasare
    7 months ago

    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

4 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    7 months ago

    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

     

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    7 months ago

    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)] )
  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    7 months ago

    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's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    7 months ago

    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