Forum Discussion

stuartp22's avatar
stuartp22
New Member
5 years ago
Solved

Calculate average price increase

I've created a Power BI visual to show how the cost prices of products have increased over time.  I have a product table and a purchase order lines table, and have output all purchased products, and ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    The measure you need for your card is likely one like the one below.  Replace Table[ProductID] with the table/column that has your product id or name.  This will create a virtual table of all your products (in scope of any slicers you have), calculate the price increase for each, and then average all those values together.

     

    Avg Product Increase =
    AVERAGEX (
        DISTINCT ( Table[ProductID] ),
        VAR lstcst = [Last Cost]
        VAR frstcst = [First Cost]
        RETURN
            DIVIDE ( lstcst - frstcst, frstcst )
    )

     

    The use of variables prevents it recalculated [First Cost] twice.

     

    Pat

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

A couple thoughts, but i'm by no means an expert, so take them with a grain of salt... 

 

If you truly want the nominal average of your Cost Increases... you could make "Cost Increase" an added column in your table (rather than a measure) and then just do a measure for "Average Cost Increase = AVERAGE(Cost Increase)"... 

I'm not sure that's the number you want, it might be, and will give you 75% in your case... but you might also be looking for the average cost increase across all products not as an average of percentages, but an average of cost value... the difference being:

Average Cost Increase as you have defined = (100+50)/2 = 75%
Average Cost Value Increase (you might want) = (85-55)/55 = ~55%

 

If you want the latter, then you could simply do the following in a measure:
Average Cost Value = DIVIDE( (SUM(Last Cost)-SUM(First Cost)), SUM(First Cost) )


The difference between the two (and you need to decide which you want) is:
75% = The average pricing increase you've made
55% = The average amount more people are paying for their products

  • stuartp22's avatar
    stuartp22
    New Member
    5 years ago

    Thanks for this write-up, it has really helped me think more clearly about what I need to present and on balance I think the 2nd option is what I am really looking to show.  However I have a couple of difficulties with how to implement these in Power BI.

     

    > you could make "Cost Increase" an added column in your table

     

    Unless I'm missing something I don't see how I would do this, given that first and last cost have to be measures.  The table of data is just purchase order lines.  Is there a way I can somehow get the first and last cost represented as columns in the data?  But then the users want the flexibility to choose the cut off dates so I don't think it can be done this way.

     

    > If you want the latter, then you could simply do the following in a measure:
    > Average Cost Value = DIVIDE( (SUM(Last Cost)-SUM(First Cost)), SUM(First Cost) )

     

    This is more what I want, except Power BI does not let me use SUM on the measures.  How can I get the sums of the first and last cost measures to do this calculation?  I tried to implement the solution here https://community.powerbi.com/t5/Desktop/Sum-of-a-calculated-measure-column/td-p/446458 but although I get an answer, it is the wrong one.

     

    Thanks