Help with average price non-promo calculation (static) vs. promo price

[Anonymous]

Hi Community,

Thank you Vanessa for helping with the % Lifts.

I am trying to develop a measure in which the sale price for a specific week is X cheaper than the non-promo price. I would like the non-promo price to stay static. So when a week on the bar graph is selected, it doesn't change and the formula for computing the difference in promo vs. non promo price.

Price difference = [promo price] - [non-promo price]

Other formulas that may impact:

1. TY Dollars = sum('LOWES Dol Sales'[Sales ($)])
2. TY Non-Promo Dollars = calculate(sum('LOWES Dol Sales'[Sales ($)]),filter('LOWES Dol Sales','LOWES Dol Sales'[Promotion]="no"))
3. TY Promo Dollars = calculate(sum('LOWES Dol Sales'[Sales ($)]),filter('LOWES Dol Sales','LOWES Dol Sales'[Promotion]="yes"))
4. Avg Promo Price = divide([TY Promo Dollars],[TY Promo Units],0)
5. Avg Non-Promo Price = divide([TY Non-Promo Dollars],[TY Non-Promo Units],0)

   Correct when all weeks selected belowWant to read $24.9 when any week in chart below is selected.

The second picture, I also edited the card hoping that it would keep that value when doing the price difference calculation. I have tried to use var calc, All, allexcept, filters on the price measures and the difference, but nothing changes.

I am guessing the calculation is more complicated that I thought - maybe requires multiple measures aside from what I have already. Var calc or AverageX can solve it? Any thoughts? Thank you in advance.

[Anonymous]

Hi @Anonymous ,

Price difference =CALCULATE( [promo price] - [non-promo price],ALL('Table'))

Better to provide more details about your table and your problem or share me with your pbix file after removing sensitive data to help us clarify your scenario.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi Eyelin,

Thank you for your answer. I did that before - with adding the column (promotion). I left it in there so you can take a look.

Here is a link to the file. 

https://www.dropbox.com/s/3pbcp6iw3upr3m4/Lowes%202021%20Fiscal%20Year%20-%20Version%204.4.pbix?dl=0

Hope this helps - thanks for looking into it.