Getting sales from another table

[Adamzzzz]

Hello, 

        I receive daily sales data from a retail store for products and units sold in a 'Sales product table'.

I am trying to determine the total value using a calculated column (if this could be done with a measure I am interested in hearing this solution)

The total value should be straight forward as I have the value of the product in the product description. So it is simply the value of the product * units sold.

Except I have products that have a variable value. This is where I am lost.

The value of the variable products is located in another table- 'Variable product table'. I am trying to bring the total value by day for variable into the Sales product table. My end result would look like 'End result table'

I had a solution where I concatenated storeid and date to create a relationship between the tables and used a switch function to work out the value, but I am dealing with millions of rows. This took up a lot of space and was not feasible for publishing.   

Thank you for any assistance. 

Sales product table
StoreID	Product	Units Sold	Date
1	$100 Product	2	1/1/2016
1	$200 Product	1	1/1/2016
1	$300 Product	4	1/1/2016
1	Variable Product	3	1/1/2016

Variable product table
StoreID	Product	Units Sold	Sales Value	Date
1	Variable Product	1	115	1/1/2016
1	Variable Product	1	187	1/1/2016
1	Variable Product	1	500	1/1/2016

End result table
StoreID	Product	Units Sold	Date	Sales Value	Comment
1	$100 Product	2	1/1/2016	$200	$100 product * 2 Units sold
1	$200 Product	1	1/1/2016	$200	$200 product * 1 Units sold
1	$300 Product	4	1/1/2016	$1,200	$300 product * 4 Units sold
1	Variable Product	3	1/1/2016	$802	Sum of sales value column from 'Variable product table' for 1/1/2016 and StoreID1

[Vvelarde]

hi @Adamzzzz

I solved this following this steps:

1. Go to Edit Query and make a copy of Sales Products Table. Rename to Products.

2: In Product Table, delete all the columns except Product Column.

3: Delete duplicate rows. Close & Apply

4: Make relationships between Sales Product & Product also Variable Product & Product

5: Create a measure Price Product

PriceProduct = if(and(HASONEVALUE('Sales Product'[Product]);LEFT(VALUES('Sales Product'[Product]))="$");Mid(VALUES('Sales Product'[Product]);2;FIND(" ";VALUES('Sales Product'[Product]);1)-1))

6: Create a measure SalesValue

SalesValue = If('Sales Product'[PriceProduct]=BLANK();sum('Variable Product'[Sales Value]);'Sales Product'[PriceProduct]*sum('Sales Product'[Units Sold]))

Finish. 😃

[Adamzzzz]

Thank you Vvelarde

[Adamzzzz]

My solution that added a lot of space to the model due to the key (Date&StoreID) was as follows-

=SWITCH(TRUE(),

Sales product table[Product]="Variable Product",

(RELATED('Variable product table'[Sum of Sales Value])),

'Sales product table'[Product]="$100 product",(100*'Sales product table'[Units Sold])..............

[Adamzzzz]

Any help would be appreciated.