Forum Discussion

Buyer_JC78's avatar
Buyer_JC78
Regular Visitor
2 years ago
Solved

Formula not working - Power BI Desktop

I need help with a Power BI formula.

I'm trying to add a column which calculates a negative value from column A (Invoice Value) if column B states "credit note" or if column B states "invoice" then the value remains positive. I'm using the following formula but I don't think this is correct as it's not pulling anything back to my new column.

 

IF(SELECTEDVALUE(U5PURCHASINGGRID[INVType])="CREDIT NOTE",(U5PURCHASINGGRID[INVOICE VALUE (LINE)]*-1.00))
 
Can anyone help please?
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Buyer_JC78 -Can you try below calclulated column to reflects the negative values for "CREDIT NOTE" and positive values for "INVOICE".

     

    NewColumn = IF(TRIM(U5PURCHASINGGRID[INVType]) = "CREDIT NOTE", U5PURCHASINGGRID[INVOICE VALUE (LINE)] * -1, U5PURCHASINGGRID[INVOICE VALUE (LINE)])

     

    Hope it works

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Buyer_JC78 -Can you try below calclulated column to reflects the negative values for "CREDIT NOTE" and positive values for "INVOICE".

     

    NewColumn = IF(TRIM(U5PURCHASINGGRID[INVType]) = "CREDIT NOTE", U5PURCHASINGGRID[INVOICE VALUE (LINE)] * -1, U5PURCHASINGGRID[INVOICE VALUE (LINE)])

     

    Hope it works

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Sergii24's avatar
    Sergii24
    Super User
    2 years ago

    Are you creating a calculated column? If so, the follwoing code should work:

     

    IF(
   U5PURCHASINGGRID[INVType] = "CREDIT NOTE",
   U5PURCHASINGGRID[INVOICE VALUE (LINE)] * -1.00,
   YOUR ELSE STATEMENT (for instance, U5PURCHASINGGRID[INVOICE VALUE (LINE)] or BLANK() )
)

     

     

     

     

    You can do so, because when you create a calculated column you have the access to the currently selected row. SELECTEDVALUE() formula actually provides you an output, which is blank. The reason it's blank is because  columns INVType and INVOICE VALUE (LINE) in your table are not filtered.


    You need to remember that row context provides access to the row of a table, but is not filtering the table.

     

    Make sure you understand well these few concepts before moving ahead in working with PowerBI, it will save you dozens of hours!

     

    I hope you'll find it helpful! Have a great day and good luck with your project 🙂

    • Thejeswar's avatar
      Thejeswar
      Super User
      2 years ago

      Hi Buyer_JC78 ,

      While using Calculated Columns you don't have to use SELECTEDVALUE() function as you will already have the row context with a calculated column. Sergii24 has given a good explanation for this.

  • Buyer_JC78's avatar
    Buyer_JC78
    Regular Visitor
    2 years ago

    Thank you rajendraongole1, the formula worked! You've saved me lots of time.

    Thanks to all for the great advice and links too.