Multiply every entry in column in one query with a fixed number from another query

[agupta2481]

Multiply every entry in column in one query with a fixed number from another query

Hi, 

one have one query where in there are five rows and five columns. Rows are for names of sales person and columns are for the "Part number" of the product being sold as shown in the above image . Each row carries the name of the sales person and each column carries the units sold by each sales person.

Then there is another query  and in this query there is only three columns, 1st Columns has the heading as "Product",, 2nd Column has the heading "Product Name" and the 3rd columns has the cost Price. 

How can i get the cost price multiplied with all the units sold in single column. the Cost Price is variable and will change on monthy basis.

this is a small data figure from large data set. 

[agupta2481]

Hi Ashish,

Thanks for the quick Response. 

Please Downlaod the files from the link https://we.tl/t-7XrHE8V5iV

3rd Worksheet contains the desired result from earlier two work sheets.

[Ashish_Mathur]

Hi,

Select the first 2 columns of Query 1, right click and select "Unpivot Other Columns".  Rename the Attribute column to Product Code and Value column to Quantity.  Append Query 1 and Query 2 into a third Query.  Remove all columns other than the Product Code column.  Change case of that single column to Upper case and Remove duplicates.  In Query 1, enter this calculated column formula

Cost = lookupvalue('Query 2'[Actual Cost],'Query 2'[Product Code],'Query 1'[Product Code])

Amount = 'Query 1'[Cost]*'Query 1'[Quantity]

Write this measure and drag it to your visual

Total = sum('Query 1'[Amount])

[Ashish_Mathur]

Hi,

The images are ust way too small.  Cannot understand a thing there.  Put that information in an MS Excel file and share the download link of that file.  Explain your requirement via comments boxes in that Excel file.