Forum Discussion
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 the earliest and latest cost prices between dates I can specify with sliders:
- First Cost = CALCULATE(MIN(K8_PC_01_LINES[COSTPR]),FIRSTDATE(K8_PC_01_LINES[Receipt Date]))
- Last Cost = CALCULATE(MAX(K8_PC_01_LINES[COSTPR]),LASTDATE(K8_PC_01_LINES[Receipt Date]))
So far, so good, and I get output similar to the below (hugely simplified) example:
| Product | First Cost | Last Cost | Cost Increase |
| P1 | 5 | 10 | 100 |
| P2 | 50 | 75 | 50 |
I now need to add a card to show the average percentage increase across all products and this is where I am struggling.
In my simple example above, is it mathematically correct to say the average price increase is 75% i.e. an average of the percentage increases?
Please can someone advise how I would write a formula to work this out? I have a Cost Increase measure:
- Cost Increase = DIVIDE(([Last Cost]-[First Cost]),[First Cost])
However I cannot figure out how to write another measure that works out the average of this one (if that's even the right way to do it)
Thanks
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
4 Replies
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
Thanks so much for your help with this and sorry it's taken a while to post back.
In implementing this, I realised that zeros and negative values were skewing my calculation, but I found out how to exclude those, and also learned about using variables in DAX 🙂
Much appreciated
Cheers!
Stuart
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 productsThanks 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
