TheOnlyJozinho
New member
- Joined
- Jan 30, 2015
- Messages
- 1
- Reaction score
- 0
- Points
- 0
I'm trying to build up a Calculated Column that calculates the Average Price (so I can calculate the deviation of each row from that average) across the sub-set of rows that have the same ProductID, YearID and ChannelID as the current row-context.
First, I tested the formula below using a fixed value, before going on to replace it with a variable for the current row-context. However, this syntax returns the price of the current row i.e. It is calculating the average of the current row...rather than all rows with the same IDs.
Anyone help, and then help me generalise it so it works for all IDs? I tried using All(Table) but that overrides the other ID filter expressions.
What am I missing?
In an MDX context, this would be the deviation of the child from it's siblings within a parent effectively.
=CALCULATE(AVERAGEX(Table,Table[Price]),Table[ProductID]=57299,Table[YearID]=106,Table[ChannelID]=0)
I will then have Table[Price] - Average[Price] for every row based on it's siblings
First, I tested the formula below using a fixed value, before going on to replace it with a variable for the current row-context. However, this syntax returns the price of the current row i.e. It is calculating the average of the current row...rather than all rows with the same IDs.
Anyone help, and then help me generalise it so it works for all IDs? I tried using All(Table) but that overrides the other ID filter expressions.
What am I missing?
In an MDX context, this would be the deviation of the child from it's siblings within a parent effectively.
=CALCULATE(AVERAGEX(Table,Table[Price]),Table[ProductID]=57299,Table[YearID]=106,Table[ChannelID]=0)
I will then have Table[Price] - Average[Price] for every row based on it's siblings