cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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 😊

1 ACCEPTED SOLUTION
Super User

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

6 REPLIES 6
Super User

Hi @Ttaylor9870

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

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.

Super User

@Ttaylor9870
Can you share a dummy sample file?

Helper III

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

Super User

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

Helper III

Hi @tamerj1 ,

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

Many thanks once again!

Taylor