Forum Discussion
Calculate average price increase
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
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