cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Subtraction multiplication and sum of variables in table

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Subtraction multiplication and sum of variables in table

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

13 REPLIES 13
Super User IV

## Re: Subtraction multiplication and sum of variables in table

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.

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Super User IV

## Re: Subtraction multiplication and sum of variables in table

Create a new column like this

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

Then sum it up.

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

## Re: Subtraction multiplication and sum of variables in table

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?

Helper III

Super User IV

## Re: Subtraction multiplication and sum of variables in table

@Neill_ 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])

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Super User IV

## Re: Subtraction multiplication and sum of variables in table

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

like

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.

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

## Re: Subtraction multiplication and sum of variables in table

@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?

Helper III

## Re: Subtraction multiplication and sum of variables in table

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

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."
Super User IV

## Re: Subtraction multiplication and sum of variables in table

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors