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[LineTota...
Need Help
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?

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi PSRai ,

      Have you solved your problem?

      If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

      If you still need help, please share some data sample which could reproduce your sceanrio and your desired output so that we could help further on it.

      Best Regards,

      Cherry