cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Neill_ Helper III
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
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

13 REPLIES 13
Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


Neill_ Helper III
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?
 

 

Neill_ Helper III
Helper III

Re: Subtraction multiplication and sum of variables in table

 

 

Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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


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.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


Neill_ Helper III
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?

 

 

Neill_ Helper III
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:
 
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."
Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors