Forum Discussion

Pablo_Ross's avatar
Pablo_Ross
Frequent Visitor
2 years ago

Price Impact Measure Help Needed

Hi,   I am trying to create a measure that will help me determine the YoY Price Impact on a large number of items - So essentially, how has the Average Selling Price changed from Current Period to ...
Measure Help
FlipFlop1's avatar
FlipFlop1
Icon for Advocate I rankAdvocate I
2 years ago

Here is an example with ContosoRetail Sales

Create Measures:

1. Sales = SUM(FactOnlineSales[SalesAmount])
2. Sales LY = CALCULATE(SUMX(FactOnlineSales, (FactOnlineSales[SalesAmount])), SAMEPERIODLASTYEAR(DimDate[Datekey]))
3. Qty = SUMX(FactOnlineSales, FactOnlineSales[SalesQuantity])
4. Qty LY = CALCULATE(SUMX(FactOnlineSales, FactOnlineSales[SalesQuantity]), SAMEPERIODLASTYEAR(DimDate[Datekey]))
 
Then create the Price Impact Measure. Something like this.

Price Impact =
               IF(AND(FactOnlineSales[Sales] = 0, [Sales LY]=0), 0,
               IF(AND(FactOnlineSales[Sales]>0, [Qty]=0), FactOnlineSales[Sales]-[Sales LY],
               IF(AND(FactOnlineSales[Sales]>0, [Qty]>0), (FactOnlineSales[Sales]/[Qty])-((FactOnlineSales[Sales LY]/[Qty LY])*[Qty LY])
)))