cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Correct Total for Averages (Price Volume Mix Analysis)

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)

      PVM Example.JPG

 

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... 

2 ACCEPTED SOLUTIONS
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

View solution in original post

6 REPLIES 6
Frequent Visitor

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...

2020-11-09 12_14_04-Price Volume Mix analysis - Power BI Desktop.png

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

2020-11-09 12_17_06-Price Volume Mix analysis.xlsx - Excel.png

Here are Excel and PBI files:

Google drive files 

 

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 

Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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. 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors