Need help with price effect calculation

[fallingfirst]

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

[fallingfirst]

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

[v-xinc-msft]

Hi @fallingfirst ,

Thank you for your kind reply! Could you tell us why the result is 420 and share your logic with us?

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

[fallingfirst]

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

[v-xinc-msft]

Hi @fallingfirst ，

Thank you for your kind reply!

Could you please explain how the value of 1820 in the 'Price Impact' column was derived? According to the logic and the data you provided, the calculation for price impact should be (Price CY - Price PY) * Units CY. Therefore, the price impact for Product A should be 1600 + 0 + 230 = 1830?

Did you not provide us with the complete table data? You calculated separately for YTD and MTD, but your data table only contains monthly data and does not have more complete data regarding dates!

Could you pleaseshare your Excel spreadsheet or your sample data file with us. Thank you for your understanding!

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,

Lucy Chen

[fallingfirst]

The below table shows price calculation performed on the monthly and YTD sales. I am only doing a simulation in excel as I am unable to share my company's data model.

Product A	SalesCY	UnitsCY	PriceCY	SalesPY	UnitsPY	PricePY	Price Impact
1	5000	200	25.00	3060	180	17.00	1,600
2	4620	210	22.00	4180	190	22.00	0
3	5750	230	25.00	5040	210	24.00	230
YTD	15370	640	24.02	12280	580	21.17	1,820

[v-xinc-msft · Accepted solution]

Hi @fallingfirst ,

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.

[v-xinc-msft]

Hi @fallingfirst ,

Please check.

We recommend you to use the code.

PriceImpact = SUMX('Table', ([Price CY] - 'Table'[Price PY]) * [Units CY])

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.

[audreygerred]

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.