Forum Discussion

PSRai's avatar
PSRai
Helper III
7 years ago

Formulas

HI All, 

 

I'm fairly new to PowerBI and Im struggling to get a formula to work, Im hoping one of you experts can help me 

 

IF(SUM('Order Data'[DocumentTypeID])=1,(SOPInvoiceCreditLine[LineTotalValue]),(SOPInvoiceCreditLine[LineTotalValue]*-1))
 
If DocumentTypeID is 1 then  *-1 to LineTotalVal
 
I have wrote this formula in PowerBI however its adding a - to all figures and not to the figure that has a condition,
 
Please can someone help?
Need Help

4 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    The problem is that this formula is not checking the value of DocumentTypeID on a row by row basis, you are adding the DocumentTypeID for all rows and then checking if the sum of those is equal to 1 (which it won't be).

     

    The way to solve this is to use SUMX to evaluate this expression on a row by row basis.

     

    eg.

     

    SUMX('Order Data',
        IF('Order Data'[DocumentTypeID]=1
          , SOPInvoiceCreditLine[LineTotalValue]
          , SOPInvoiceCreditLine[LineTotalValue] * -1)
    )

    • PSRai's avatar
      PSRai
      Helper III
      7 years ago

      Hi

       

      Thanks, I have tried that but still not working, 

      Their is a minus figure applied to all figures instead of the one with the condition.

       

      See diagram below. 

       

      So the very last figure should be a negative also the figures in Line Val ADJ should match those in LineTotalValue

       

      Hope you can help 

       

      Line Val ADJ

      • d_gosbell's avatar
        d_gosbell
        Super User
        7 years ago

        What's the relationship between 'Order Data' and 'SOPInvoiceCreditLine' can you post a picture of the table diagram?

         

        Is [LineTotalValue] a measure or a column?