Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to Solution.
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] )
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |