Forum Discussion
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 in the product description column.
If the product description has the keyword "package" listed in any row for that invoice #, I need the Invoiced Price to display what's listed in the Total cost column for the row that has the keyword "package" and to list zero for everything else listed on that particular invoice #. If the product description does not have the keyword package, I need it to list everything from the total cost column in the Invoiced Price.
In the example below, Invoice # 2025-0001 is a TV Package and everything that's included in that package (tv, tv mount, speakers, and HDMI cables). Since the package was purchased, the Invoiced price should show the TV package total cost of $4500 and everything that's included in that package deal (tv, tv mount, speakers, and HDMI cables) should show as $0. Invoice # 2025-0008 was not a package deal, so I would need the Invoiced Price to show what's listed in the total cost column.
| Invoice # | Invoice Date | Item # | Product Description | Qty | Price | Total Cost | Invoiced Price |
| 2025-0001 | 1/25/25 | PKG-TV | TV Package | 1 | $4,500.00 | $4,500.00 | $4,500.00 |
| 2025-0001 | 1/25/25 | TV-01 | 100" TV | 1 | $3,000.00 | $3,000.00 | $0.00 |
| 2025-0001 | 1/25/25 | MNT-01 | TV Mount | 1 | $50.00 | $50.00 | $0.00 |
| 2025-0001 | 1/25/25 | SPKR-01 | S.S. Speakers | 1 | $1,600.00 | $1,600.00 | $0.00 |
| 2025-0001 | 1/25/25 | HDMICBL-01 | HDMI cable | 4 | $15.00 | $60.00 | $0.00 |
| 2025-0008 | 4/9/25 | TV-05 | 75" TV | 1 | $2,500.00 | $2,500.00 | $2,500.00 |
| 2025-0008 | 4/9/25 | MNT-80 | TV Mount | 1 | $55.00 | $55.00 | $55.00 |
Your help is greatly appreciated!
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) > 0RETURN
IF(
HasPackage > 0,
IF(IsPackageRow, 'Table'[Total Cost], 0),
'Table'[Total Cost]
)
4 Replies
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) > 0RETURN
IF(
HasPackage > 0,
IF(IsPackageRow, 'Table'[Total Cost], 0),
'Table'[Total Cost]
)Thank you! This worked!
Hi Chipsahoy1,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay PabbuHi @Chipsahoy1,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu
