Price Impact Measure Help Needed

[Pablo_Ross]

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 the Same Period Last Year. However, there are 3 variables that I need to consider. These being:

1) If Sales LY = 0 and Sales CY = 0, then return Price Impact of 0 (or blank)

2) If Sales > 0 but Quantity = 0, then return Price Impact of CY Price - LY Price

3) If Sales > 0 and Quantity > 0, then return Price Impact of (CY Price / CY Quantity) - (LY Price / LY Quantity) * LY Quantity

I am relatively new to BI so would appreciate some help creating a measure that would allow me to complete this with all 3 variables above considered.

I appreciate the help in advance.

Thank you!

[FlipFlop1]

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])

)))

[Pablo_Ross]

Hi, first  
of all, thank you for your reply.

I'm unable to share the file I'm working with currently.

This measure would be used in conjunction with a Month Year Slicer that allows users to select the period that they want to see the Price Impact for.

This would therefore mean CY = The sales in current Month Year selected vs Same Period Last Year.

There are also different customer types (called activities) in my images below, however, all sales figures (sales and quantity sold) are pulled from the 1 table for all customer types, item types etc.

Please let me know if you require anything else and thank you again for helping me with a formula to capture the 3 conditions from my original post.

Here is an example of what slicers I have / how the report looks currently. I need a Price impact measure to add in to the end of this table.

[some_bih]

Hi @Pablo_Ross please provide example data with inputs and expected output, together with model and relationship.

Should these measure to be tracked on monthly / yearly level or product, customer...another dimension provide some details.

File would be the best