Subtraction multiplication and sum of variables in table

[Anonymous]

Hi,

I have a table for average prices and weights sorted by the Start of Week Date (SoW)

I'm hoping to acheive something like this in the form of a new column 

(Average Price Paid - GB SQQ Price * Total Intake Data Cold Weight = Additional Cost Total)

I'm hoping to find the overall sum of this Additional Cost Total column i.e. 

Can anyone help?

[amitchandak]

Create a new column like this

Additional Cost Total = ([Average Price Paid] - [GB SQQ Price]) * [Total Intake Data Cold Weight]

Then sum it up.

[Greg_Deckler]

Should be able to create a column in the table:

Additional Cost Total = [Average Price Paid] - [GB SQQ Price] * [Total Intake Data Cold Weight]

And then just use a default SUM aggregation to the get total for the entire column.

[Anonymous]

So I have written a formula:

Additional Cost Total = ('Sheet1 (2)'[AverageIntakeDataPrice]) - ('GB SQQ Price Data'[Average SQQ Price YTD]) * ('Sheet1 (2)'[Intake_Data_Cold_Weight])

NOTE: I'm using variables from two separate tables (1) Sheet1 (2) & (2)GB SQQ Price Data. I have created a relationship based on SoW between the two tables. But I keep getting this error:

"This expression refers to a Column object named 'GB SQQ Price Data[Column]', which has an error."

Would the layout of the data be an issue?

[amitchandak]

You need to do it on a common dimension. That dimension can be date and product etc

like

Additional Cost Total =

sumx(summarize(Table,Table[SoW],"_1" ,max('Sheet1 (2)'[AverageIntakeDataPrice]) , "_2",max('GB SQQ Price Data'[Average SQQ Price YTD]) ,"_3", sum('Sheet1 (2)'[Intake_Data_Cold_Weight])),([_1]*[_2])*[_3])

This table is a common dimension.

[Anonymous]

I've written this into a new column in the GB SQQ Price Data table:

Additional Cost Total edit =

sumx(summarize('Start of Week Sort','Start of Week Sort'[SoW],"_1" ,max('Sheet1 (2)'[AverageIntakeDataPrice]) , "_2",max('GB SQQ Price Data'[Average SQQ Price YTD]) ,"_3", sum('Sheet1 (2)'[Intake_Data_Cold_Weight])),([_1]*[_2])*[_3])

and this error pops up?

"Column 'AverageIntakeDataPrice' in table 'Sheet1 (2)' cannot be found or may not be used in this expression."

[Greg_Deckler]

@Anonymous Any way you can share your PBIX? This can likely get taken care of fairly easily but it is very difficult without being able to see your table structures, data and how they can be related to one another.

[Anonymous]

@amitchandak 

@Greg_Deckler 

Would this work?

[Greg_Deckler · Accepted solution]

@Anonymous - I did this:

Measure 2 = (AVERAGE('Sheet1 (2)'[Intake_Data_Price]) - [Average SQQ Price YTD]) * SUM('Sheet1 (2)'[Intake_Data_Cold_Weight])

See attached, not coming up with exactly your numbers.

[Anonymous]

@Greg_Deckler 

I'm having a similar issue again with the summation of total "COP Cost YTD" i.e. in this formula (X-Y *Q)

As you suggested I have built a measure:

Measure 3 = (([Filtered Average Intake Price]) - [Min Industry Start Value £]) * ([Filtered Total Intake Data Cold Weight])

and then 

COP Cost YTD. = IF(HASONEVALUE('Start of Week Sort'[SoW]),[Measure 3],

SUMX(

SUMMARIZE(

'Start of Week Sort',

"__Measure",[Measure 3]

),

[__Measure]

))

Any ideas?

[amitchandak]

@Anonymous , I agree with @Greg_Deckler , Share a pbix.

I doubt the Start of Week Sort is not joined to Sheet1 (2) the table for which it gives error

[Greg_Deckler]

@Anonymous What table are you creating this new column in? You will likely need to use RELATED or RELATED table, like:

Additional Cost Total = ('Sheet1 (2)'[AverageIntakeDataPrice]) - RELATED('GB SQQ Price Data'[Average SQQ Price YTD]) * ('Sheet1 (2)'[Intake_Data_Cold_Weight])

[Anonymous]

@Greg_Deckler I was hoping to create it in the GB SQQ Price Data table as I need the data in the Monday Start of each week format:

So when I enter this formula, the following is underlined

Additional Cost Total = ('Sheet1 (2)'[AverageIntakeDataPrice]) - RELATED('GB SQQ Price Data'[Average SQQ Price YTD]) * ('Sheet1 (2)'[Intake_Data_Cold_Weight])

But I get the error that I need to create a relationship: The column 'GB SQQ Price Data[Average SQQ Price YTD]' either doesn't exist or doesn't have a relationship to any table available in the current context.

However as Average SQQ Price YTD is different I'm not sure what to relate it to?

Should I just create a brand new table pulling in each of these variables that I need?