Correct Total for Averages (Price Volume Mix Analysis)

[carguello726]

Hi,

I am doing a price-volume-mix analysis and I am struggling to get a correct grand total. This analysis is done at a part number level (pbix link below) 

For simplicity, I am focusing just on the Price Effect calculation. It is calculated by

• Price = Quantity Current Year * (Average Price CY – Average Price PY)

2 desired goals:

1. Sum the line item results across all part numbers to get a correct grand total
2. Have the ability to summarize by Material Group (take the sum of the part number level results)

      

FYI I found this potential solution online but it also did not work.

Price Effect v2 (Still Wrong) =

VAR Price = [Quantity CY] * ( [Average Price CY] - [Average Price PY] )

VAR PriceTotal = SUMMARIZE( Data, Data[Product ID], "Price Effect", Price)

RETURN

IF( HASONEVALUE( Data[Product ID] ),

       Price,

           SUMX( PriceTotal, [Price Effect]))
File: https://thearguellos-my.sharepoint.com/:u:/g/personal/carguello726_thearguellos_onmicrosoft_com/Ebci... 

[mahoneypat · Accepted solution]

Please give this approach a try with your original measure (the one used in your table that shows wrong total).

NewMeasure = sumx(values(Data[Product ID]), [Price Effect Measure])

For your Material Group, put that new measure in similar pattern - sumx(Values(Data[Material Group]), [NewMeasure])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[spock1984]

Hello @carguello726 !

Can you share with us content of [Price Effect Measure]? I am struggling with same problem 😞

I understand the solution proposef by @mahoneypat worked?

I have an issue  - see that I can calculate Price effect indivudially on each Unit, but total is not correct. I think I should use SumX, but it does not work...

Total Price effect should be sum of -1.186 - 2.453 + 1.128 = -2.512

Here are Excel and PBI files:

Google drive files 

[spock1984]

I think I figured this out. I had to create two measures:
Price effect generic = ([Price Y+1]-[Price Y])*[Volume Y+1]
Price effect = sumx(values(Amounts[Unit]), [Price effect generic])
Works well with second formula 🙂 thanks for hint @mahoneypat 

[mahoneypat · Accepted solution]

Please give this approach a try with your original measure (the one used in your table that shows wrong total).

NewMeasure = sumx(values(Data[Product ID]), [Price Effect Measure])

For your Material Group, put that new measure in similar pattern - sumx(Values(Data[Material Group]), [NewMeasure])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[LucaA]

Hi @mahoneypat , @carguello726 

ty i solved my problem related to the price effect following your advice. I followed it also for volumes but then when i get to the mix effect something is not working as i would like. My database is similar to the original poster's one. I would like to see a mix effect which should be 0 at product level (as it is the lowest possible level) but should have a value at the "material group" level. 

[mahoneypat]

Would it work to just work HASONEVALUE() into the mix?

ZeroProduct = IF(HASONEVALUE(Table[Product]), 0, [YourMeasure]) //or use BLANK() instead of 0

Regards,

Pat

[carguello726 · Accepted solution]

Thank you so much. You are a life saver. Simple and effective. Any idea why my previous formula did not work (using summarize table function)?