Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Ttaylor9870
Helper III
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 NameProduct NameAverage Selling PriceFree Stock QuantityStock Value
BobApples£98.1210,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

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

1.png

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @Ttaylor9870 
Please try

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

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.

@Ttaylor9870 
Can you share a dummy sample file?

Here is the relationship below...

Ttaylor9870_1-1676639694532.png

 

And here are the tables...

Nav_Sales Line:

No_Production GroupAverage Selling PriceProduct NameDocument No_Outstanding Quantity

ABCLego1BlueSO123433
ABCLego2GreenSO123544
ABCLego3GoldSO123655
ABCLego4BlackSO123766
ABCLego5SilverSO123877
JJJCars1RedSO123922
JJJCars2BlueSO123133
JJJCars3GreySO123211

 

Nav_Item:

No_Production GroupAverage Selling PriceProduct Name

ABCLego1Spiderman
JJJCars2Optimus Prime

 

Nav_Item Ledger Entry

Item NoQuantity

ABC2
ABC3
ABC6
JJJ7
JJJ2
JJJ3

 

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...
Ttaylor9870_2-1676640072951.png

 

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

1.png

Hi @tamerj1 ,

 

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

 

Many thanks once again!

 

Taylor

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.