Forum Discussion

tedbuny's avatar
tedbuny
Frequent Visitor
1 year ago
Solved

Custom Measure in Report Displays G-14

Hello - 

I have a custom measures that are displaying G-14 rather than 0. Ideally if nothing was delivered the measuers should be zero.
I've changed formatting and messed around with some other settings but still no luck. It's a division by zero but I can't get it to display zero in these situations. Any help would be appreciated. 

 

Est. Net Cases = CALCULATE( IF (SUM('Actual Sales'[NetCaseQuantity]) = BLANK(), 0, SUM('Actual Sales'[NetCaseQuantity])) , FILTER  ('Product', 'Product'[Product Number]  <> {"996001"} ) , FILTER  ('Product', 'Product'[Product Number]  <> {"996002"})
 
Cases Delivered = CALCULATE( IF (SUM('Actual Sales'[NetQuantity]) = BLANK(), 0, SUM('Actual Sales'[NetQuantity])) , FILTER ( 'Product', 'Product'[Product Number] = "996001" ))
 
Est. Case Variance = CALCULATE(IFERROR([Cases Delivered] - [Est. Net Cases],0)) 
 

 

Need Help
  • d_m_LNK's avatar
    d_m_LNK
    1 year ago

    What happens if you rewrite your measure like this:

    Est. Net Cases = 

    CALCULATE( IF (SUM('Actual Sales'[NetCaseQuantity]) = BLANK(), 0, SUM('Actual Sales'[NetCaseQuantity])) , NOT  'Product'[Product Number] IN {"996002","996001"})

3 Replies

  • d_m_LNK's avatar
    d_m_LNK
    Icon for Super User rankSuper User
    1 year ago

    What happens if you rewrite your measure like this:

    Est. Net Cases = 

    CALCULATE( IF (SUM('Actual Sales'[NetCaseQuantity]) = BLANK(), 0, SUM('Actual Sales'[NetCaseQuantity])) , NOT  'Product'[Product Number] IN {"996002","996001"})

    • tedbuny's avatar
      tedbuny
      Frequent Visitor
      1 year ago

      This seems to work but I'll need to verify overall numbers:

       

      Est. Net Cases = ROUND(CALCULATE( IF (SUM('Actual Sales'[NetCaseQuantity]) = BLANK(), 0, SUM('Actual Sales'[NetCaseQuantity])) , NOT  'Product'[Product Number] IN {"996002","996001"}), 5)
    • tedbuny's avatar
      tedbuny
      Frequent Visitor
      1 year ago

      Thanks for the reply but it did not change anything. GE-14 still visible.