Forum Discussion
Calculate - Apply filter based on a IF condition
Hi Guys,
I don't know how to explain or title this. Let me give you my best shot.
I have a set of sales data, I want to create a measure that takes Qty * sale price per sku. Now the Issue is that the sale price changes every period.
How can I make a measure where the filter is dynamic according to the period inwhich the sale happened.
Something like this
Sales:=Qty times (sale price filtered by the sale's period price).
Does that make sense?
| Raw Data | |||
| Customer | Product | Period | Qty |
| John | Product A | Period 1 | 20 |
| Joe | Product B | Period 2 | 1 |
| Geo | Product A | Period 3 | 10 |
| Mary | Product C | Period 4 | 5 |
| Patrick | Product C | Period 5 | 8 |
| Product Table | |||
| Product | Period 1 | Period 2 | Period 3 |
| Product A | 1.5 | 1.5 | 2 |
| Product B | 1.5 | 1.55 | 2 |
| Product C | 2 | 1.5 | 2 |
Thank you in advance!!
Right click [Product] column from Product Table and select Unpivot Other Columns in Query Editor, then add a calculated column to Raw Data.
Price = LOOKUPVALUE ( 'Product Table'[Value], 'Product Table'[Product], 'Raw Data'[Product], 'Product Table'[Attribute], 'Raw Data'[Period] )Measure = SUMX ( 'Raw Data', 'Raw Data'[Qty] * 'Raw Data'[Price] )
2 Replies
Right click [Product] column from Product Table and select Unpivot Other Columns in Query Editor, then add a calculated column to Raw Data.
Price = LOOKUPVALUE ( 'Product Table'[Value], 'Product Table'[Product], 'Raw Data'[Product], 'Product Table'[Attribute], 'Raw Data'[Period] )Measure = SUMX ( 'Raw Data', 'Raw Data'[Qty] * 'Raw Data'[Price] )
