Forum Discussion

Copper Contributor

Oct 11, 2020

Incorrect Productx calculation

I have this equation: =if (contains (RELATEDTABLE('ItemMaster 1'),[ItemGroupPrefix],"Equ"), PRODUCTX(RELATEDTABLE('Invoices 1'), 0.4 * 'Invoices 1'[LineTotal]),0) this looks at a column on a...

excel

Formulas and Functions

Itchn2go

Copper Contributor

Oct 11, 2020

SergeiBaklan Thanks for the help - I am new at this and trying to learn it on the fly.

I am trying to multiply a column "LineTotal" in the Tab "Invoices 1" by 0.4 if the column in Tab "ItemMaster 1" column "ItemGroupPrefix" equals "Equ".

I am unable to get the productx function to work. I am sure it is me.

SergeiBaklan

Diamond Contributor

Oct 11, 2020

Itchn2go

Only formula is not enough. How relationships are built, based on date on something else? If date, is here separate calendar table? Are you on one or many side of relationships?

Just sample model with few records in each table could help better.

Itchn2go
Copper Contributor
Oct 11, 2020
SergeiBaklanYes, i have a calendar table along with item number master, main customer record (business partner) plus invoices and credits. see attached.
- SergeiBaklan
  Diamond Contributor
  Oct 11, 2020
  Itchn2go
  
  Thank you. But to which table you add calculated column and how this table is related to ItemMaster 1 and Invoices 1 ?
  - Itchn2go
    Copper Contributor
    Oct 11, 2020
    Thanks for the effort but I am giving up. 😞