Forum Discussion
Getting sales from another table
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 |
4 Replies
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. =)
Thank you Vvelarde
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])..............
Any help would be appreciated.
