Forum Discussion
Need help with price effect calculation
Hi, I tried using both calculated column and measure to calculate price impact but it is not returning my desired results.
My goal is to create a visualization with a slicer which would show price impact of product A & B, based on selected period.
Price impact is calcuted as (Price CY - Price PY) * Units CY.
If I create a calculated column, it will return total price impact of 430.
If I create a measure, it will return price impact of 408 (Calculation is done based on total YTD values of A & B)
The correct price impact should be 1,820 - 1,400 = 420. (Pricing impact on YTD values of A & B seperately, then added together)
Any advise wouldbe greatly appreciated
Data Model
| Month | Product | Sales CY | Units CY | Price CY | Sales PY | Units PY | Price PY | Price Impact |
| 1 | A | 5000 | 200 | 25.0 | 3060 | 180 | 17.0 | 1,600 |
| 1 | B | 5750 | 250 | 23.0 | 5520 | 230 | 24.0 | -250 |
| 2 | A | 4620 | 210 | 22.0 | 4180 | 190 | 22.0 | 0 |
| 2 | B | 4600 | 200 | 23.0 | 4500 | 180 | 25.0 | -400 |
| 3 | A | 5750 | 230 | 25.0 | 5040 | 210 | 24.0 | 230 |
| 3 | B | 5750 | 250 | 23.0 | 5980 | 230 | 26.0 | -750 |
| Total | 31470 | 1340 | 28280 | 1220 | 430 |
Visualization with monthly slicer - selected months 1 to 3
| Sales CY | Units CY | Price CY | Sales PY | Units PY | Price PY | Price Impact | |
| Product A | 15370 | 640 | 24.0 | 12280 | 580 | 21.2 | 1,820 |
| Product B | 16100 | 700 | 23.0 | 16000 | 640 | 25.0 | -1,400 |
| Total | 31470 | 1340 | 23.5 | 28280 | 1220 | 23.2 | 408 |
Hi Anonymous ,
Thank you for your kind reply!
Could you please provide a screenshot of this if it is in excel?
Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi! I created this measure and came out with 430 for the total:
Measure = SUMX('Table',('Table'[Price CY]-'Table'[Price PY])*'Table'[Units CY])What the SUMX does is allow the ('Table'[Price CY]-'Table'[Price PY])*'Table'[Units CY]) to be calculated at each row, then it adds all of those values together.Thank you all for your solutions! Unfortunately, the correct price impact should be 420 and not 430.
I guess what I need is a combination of both SUM and SUMX. The calculation should be done at the product level, but should be based on aggregated sales/units of defined period in month slicer.
This can be done in excel using calculated columns on a sliced pivot table, but I'm not sure how to replicate this in Power BI
Hi Lucy,
Thank you for your prompt response.
We would get a price impact of 420 if the calculation is done based on YTD Sales/Unit values.
If we do the calculation based on MTD Sales/Unit values, and then added up the price impact of each month, this would return 430.
Sales CY Units CY Price CY Sales PY Units PY Price PY Price Impact Product A 15370 640 24.0 12280 580 21.2 1,820 Product B 16100 700 23.0 16000 640 25.0 -1,400 Total 31470 1340 28280 1220 23.2 420
