Forum Discussion

Chipsahoy1's avatar
Chipsahoy1
Helper I
1 year ago
Solved

Changing Values Based on 2 Different Columns

Hi All,   Hoping you all could help me.  I am trying to create a new column (Invoiced Price) in my data table that changes the value from the total cost column based on the invoice # and a keyword ...
Need Help
  • Shravan133's avatar
    Shravan133
    1 year ago

    maybe create a calculated column :

    Invoiced Price =
    VAR CurrentInvoice = 'Table'[Invoice #]
    VAR HasPackage =
    CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Invoice #]),
    SEARCH("package", 'Table'[Product Description], 1, 0) > 0
    )
    VAR IsPackageRow = SEARCH("package", 'Table'[Product Description], 1, 0) > 0

    RETURN
    IF(
    HasPackage > 0,
    IF(IsPackageRow, 'Table'[Total Cost], 0),
    'Table'[Total Cost]
    )

Shravan133's avatar
Shravan133
Super User
1 year ago

maybe create a calculated column :

Invoiced Price =
VAR CurrentInvoice = 'Table'[Invoice #]
VAR HasPackage =
CALCULATE(
COUNTROWS('Table'),
ALLEXCEPT('Table', 'Table'[Invoice #]),
SEARCH("package", 'Table'[Product Description], 1, 0) > 0
)
VAR IsPackageRow = SEARCH("package", 'Table'[Product Description], 1, 0) > 0

RETURN
IF(
HasPackage > 0,
IF(IsPackageRow, 'Table'[Total Cost], 0),
'Table'[Total Cost]
)

  • Chipsahoy1's avatar
    Chipsahoy1
    Helper I
    1 year ago

    Thank you!  This worked!