cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors