Incorrect Productx calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1768234%22%20slang%3D%22en-US%22%3EIncorrect%20Productx%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768234%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20equation%3A%3C%2FP%3E%3CP%3E%3C!--%20%20%20%20StartFragment%20%20%20%20%20--%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22cf0%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf1%22%3Eif%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf0%22%3E%20(%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf1%22%3Econtains%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf0%22%3E%20(%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf1%22%3ERELATEDTABLE%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf0%22%3E('ItemMaster%201')%2C%5BItemGroupPrefix%5D%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf2%22%3E%22Equ%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf0%22%3E)%2C%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf1%22%3EPRODUCTX%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf0%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf1%22%3ERELATEDTABLE%3C%2FSPAN%3E%3CSPAN%20class%3D%22cf0%22%3E('Invoices%201')%2C%200.4%20*%20'Invoices%201'%5BLineTotal%5D)%2C0)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3C!--%20%20%20%20EndFragment%20%20%20%20%20--%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20looks%20at%20a%20column%20on%20a%20table%20called%20ItemMaster%201%20and%20if%20Equ%20is%20in%20the%20column%20it%20multiplies%20the%20data%20in%20Invoices%201%20%7BLineTotal%5D%20by%200.4.%20On%20random%20cells%20the%20data%20is%20crazy.%26nbsp%3B%20%26nbsp%3BSee%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Screen%20Shot%20of%20my%20data%22%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2Fp16nk9pdjqrpkjs%2FScreenshot%25202020-10-10%2520210858.jpg%3Fdl%3D0%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2Fp16nk9pdjqrpkjs%2FScreenshot%25202020-10-10%2520210858.jpg%3Fdl%3D0%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1768234%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768575%22%20slang%3D%22en-US%22%3ERe%3A%20Incorrect%20Productx%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827698%22%20target%3D%22_blank%22%3E%40Itchn2go%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20%3CEM%3Econtains%3C%2FEM%3E%20%3F%20It%20is%20not%20a%20built-in%20function%20as%20far%20as%20I%20know.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768782%22%20slang%3D%22en-US%22%3ERe%3A%20Incorrect%20Productx%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827698%22%20target%3D%22_blank%22%3E%40Itchn2go%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20another%20column%20to%20add%20PRODUCTX()%20only%2C%20without%20IF()%2C%20is%20it%20the%20same%20result%20or%20not%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it's%20better%20to%20have%20more%20information%20about%20the%20model%2C%20in%20DAX%20exactly%20the%20same%20formula%20gives%20different%20results%20depends%20on%20context.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1769117%22%20slang%3D%22en-US%22%3ERe%3A%20Incorrect%20Productx%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20the%20help%20-%20I%20am%20new%20at%20this%20and%20trying%20to%20learn%20it%20on%20the%20fly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20multiply%20a%20column%20%22LineTotal%22%20in%20the%20Tab%20%22Invoices%201%22%20by%200.4%20if%20the%20column%20in%20Tab%20%22ItemMaster%201%22%26nbsp%3B%20column%20%22ItemGroupPrefix%22%20equals%20%22Equ%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20unable%20to%20get%20the%20productx%20function%20to%20work.%20I%20am%20sure%20it%20is%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1769126%22%20slang%3D%22en-US%22%3ERe%3A%20Incorrect%20Productx%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827698%22%20target%3D%22_blank%22%3E%40Itchn2go%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnly%20formula%20is%20not%20enough.%20How%20relationships%20are%20built%2C%20based%20on%20date%20on%20something%20else%3F%20If%20date%2C%20is%20here%20separate%20calendar%20table%3F%20Are%20you%20on%20one%20or%20many%20side%20of%20relationships%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20sample%20model%20with%20few%20records%20in%20each%20table%20could%20help%20better.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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 table called ItemMaster 1 and if Equ is in the column it multiplies the data in Invoices 1 {LineTotal] by 0.4. On random cells the data is crazy.   See attached. 

 

https://www.dropbox.com/s/p16nk9pdjqrpkjs/Screenshot%202020-10-10%20210858.jpg?dl=0 

 

Any help is appreciated. 

11 Replies
Highlighted

@Itchn2go 

What is contains ? It is not a built-in function as far as I know.

Highlighted

@Itchn2go 

If in another column to add PRODUCTX() only, without IF(), is it the same result or not?

 

In general it's better to have more information about the model, in DAX exactly the same formula gives different results depends on context.

 

Highlighted

@Sergei Baklan  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. 

Highlighted

@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.

Highlighted

@Sergei Baklan  The answer to the question - is the  ProductX function working without any other expressions is no. i get crazy numbers.

Highlighted

@Sergei BaklanYes, i have a calendar table along with item number master, main customer record (business partner) plus invoices and credits. see attached.  

Highlighted

@Itchn2go 

Thank you. But to which table you add calculated column and how this table is related to ItemMaster 1 and Invoices 1 ?

Highlighted

Thanks for the effort but I am giving up. 

Highlighted

@Itchn2go 

Great, thank you for the update

Highlighted

@Sergei Baklan  Just an update. I got it to work.   Changed Productx to Sumx

 

Thanks. 

Highlighted

@Itchn2go 

I see, thank you