Multiplying 1 column by another - Hopefully straight forward and I am being silly

[Ttaylor9870]

Hi All,

I have 1 column in my Nav_Item table called average selling price and I would like to multiply this by my Free Stock Value measure. For some reason when I perform this return answer is wrong... Anyone have any ideas? For example...

Customer Name	Product Name	Average Selling Price	Free Stock Quantity	Stock Value
Bob	Apples	£98.12	10,208.44	£250,412.93

Measure for Free Stock Quantity is using Total Quantity measure from my Nav_ItemLedger - Total TOB Approved Unposted from my Nav_SalesLine table here are the measures below...

Free Stock Quantity = Nav_ItemLedger[Total Quantity] - [Total TOB Approved Unposted]

Total Quantity = Calculate(SUM('NAV_Item Ledger Entry'[Quantity]))

Total TOB Approved Unposted = CALCULATE(SUM('NAV_Sales Line'[Outstanding Quantity]),'NAV_Sales Line'[Location Code]="TOB")

Hopefully this is explained effectively and long story short...

'NAV_Item Ledger Entry'[Quantity]

...subtract...

'NAV_Sales Line'[Outstanding Quantity])

= Free stock Quantity and then get this Free stock Quantity * Average Sales Price....

Many Thanks,

Taylor 😊

[tamerj1]

Hi @Ttaylor9870 
Please try

NewMeasure =
SUMX ( Nav_Item, Nav_Item[average selling price] * [Free Stock Value measure] )

[Ttaylor9870]

Hi @tamerj1 , Thanks for replying!

This still gives the wrong answer, I'd assume this should give the correct one so it may be down to my data model then? I've tried everything and still can't manage to catch it.

[tamerj1]

@Ttaylor9870 
Can you share a dummy sample file?

[Ttaylor9870]

Here is the relationship below...

And here are the tables...

Nav_Sales Line:

No_Production GroupAverage Selling PriceProduct NameDocument No_Outstanding Quantity

ABC	Lego	1	Blue	SO1234	33
ABC	Lego	2	Green	SO1235	44
ABC	Lego	3	Gold	SO1236	55
ABC	Lego	4	Black	SO1237	66
ABC	Lego	5	Silver	SO1238	77
JJJ	Cars	1	Red	SO1239	22
JJJ	Cars	2	Blue	SO1231	33
JJJ	Cars	3	Grey	SO1232	11

Nav_Item:

No_Production GroupAverage Selling PriceProduct Name

ABC	Lego	1	Spiderman
JJJ	Cars	2	Optimus Prime

Nav_Item Ledger Entry

Item NoQuantity

ABC	2
ABC	3
ABC	6
JJJ	7
JJJ	2
JJJ	3

Measures I have used so far...

Total TOB Approved Unposted = SUM('NAV_Sales Line'[Outstanding Quantity])

Total Quantity = Calculate(SUM('NAV_Item Ledger Entry'[Quantity]))

Free Stock Quantity = 'Nav_Item Ledger Entry'[Total Quantity] - [Total TOB Approved Unposted]

Free Stock Value = SUMX ( Nav_Item, Nav_Item[average selling price] * [Free Stock Quantity] )

Example table of the putcome...

[tamerj1 · Accepted solution]

@Ttaylor9870 
Please clarify why do you believe the number is not correct.

[Ttaylor9870]

Hi @tamerj1 ,

Apologies this is in fact correct you're a magician!

Many thanks once again!

Taylor