Forum Discussion

Kimbornhoft's avatar
Kimbornhoft
Helper I
9 years ago

Incorrect calculated measure total

Hi,

 

I have a new measure 

 

Total Shipped Value = (sum(REV_HistoryReport[Total Li Valu Amt]) /sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled]))

 

which works correctly except when the OpenOrders[Qty Filled] field is a 0. Zero is returned but the cumulative total is not correct.  How do I add in an If/Then statement which says complete the calculation except when a 0 is present, then just return a 0? Or do I change the 0 in the OpenOrders[Qty Filled] field to a dash so that it isn't recognized as a number and just skips it.

 

Thanks for the help. 

 

 

Need Help

21 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi Kimbornhoft

     

    Ideally you can use the new DIVIDE DAX function, which will allow you to set a figure when there is a divide by zero error.

     

    Total Shipped Value =
DIVIDE (
    SUM ( REV_HistoryReport[Total Li Valu Amt] ),
    SUM ( REV_HistoryReport[Ord Bill Unit Qty] ) * SUM ( OpenOrders[Qty Filled] ),
    0
)

    This will return a zero if there are no values.

    • Kimbornhoft's avatar
      Kimbornhoft
      Helper I
      9 years ago

      Thank you for replying, unfortunately, this formula returned all lines as $0.

      • Kimbornhoft's avatar
        Kimbornhoft
        Helper I
        9 years ago

        The "0"  value appears in the multiplication side of equation if that makes a difference.