Formulas

[PSRai]

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?

[d_gosbell]

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]

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

[v-piga-msft]

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

[d_gosbell]

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?