Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
2 years ago
Solved

MoM% Showing the Infinity Value

Hi BI Community Team,   I have a sales order table and looking for MTD, PMTD & MoM Growth % with measure below that I explore and apply.   MTD-Sales = TOTALMTD(SUM('Sales Order'[Amt]), 'CALENDAR'...
Need Help
  • pmreis's avatar
    pmreis
    2 years ago

    Hi ADSL ,

    This typically occurs when you divide a number by zero. In this case, the problem arises when the [PMTD-Sales] measure returns a value of 0, which is then used as the denominator in your MoM-Sales calculation.

    Try something like this:

     

    MoM-Sales = 
VAR CurrentMonthSales = [MTD-Sales]
VAR PreviousMonthSales = [PMTD-Sales]
RETURN
IF(
    PreviousMonthSales = 0,
    BLANK(),
    (CurrentMonthSales - PreviousMonthSales) / PreviousMonthSales
)

     

    For reference on error handling in DAX formulas: DAX error reference


    Pedro Reis - Data Platform MVP / MCT
    Making Power BI and Fabric Simple

    If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

    Find me at LinkedIn

     

pmreis's avatar
pmreis
Most Valuable Professional
2 years ago

Hi ADSL ,

This typically occurs when you divide a number by zero. In this case, the problem arises when the [PMTD-Sales] measure returns a value of 0, which is then used as the denominator in your MoM-Sales calculation.

Try something like this:

 

MoM-Sales = 
VAR CurrentMonthSales = [MTD-Sales]
VAR PreviousMonthSales = [PMTD-Sales]
RETURN
IF(
    PreviousMonthSales = 0,
    BLANK(),
    (CurrentMonthSales - PreviousMonthSales) / PreviousMonthSales
)

 

For reference on error handling in DAX formulas: DAX error reference


Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

 

  • ADSL's avatar
    ADSL
    Post Prodigy
    2 years ago

    Hi pmreis ,

     

    May I need your help and advise of Card visual? That's still showing as the screenshot below, it should be zero -- 0.00 or 0%.

     

    Thanks and Regard,

     

    • pmreis's avatar
      pmreis
      Most Valuable Professional
      2 years ago

      Hi ADSL 

      You have everything right on this file you shared! It's just that the period you selected (January) doesn't have a previous month, hence the calculation for the previous month doesn't work, neither the variations as you don't have a previous month value.

      Try another month and you will see it works just fine.

       


      Pedro Reis - Data Platform MVP / MCT
      Making Power BI and Fabric Simple

      If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

      Find me at LinkedIn

       

      • ADSL's avatar
        ADSL
        Post Prodigy
        2 years ago

        Thank you for your helpful feedback and support.