Dear Forum,
I have created a calculated column to give a 'YTP Average' of monthly scores. I want this to appear as one column at the end of the monthly score columns.
The trouble is, it displays after each and every monthly column. As it's my first time creating a calculated column, I have no idea what I need to do to make it appear once only.
DAX expression: YTD Score = AVERAGEX('TeamSub-RAW-BI','TeamSub-RAW-BI'[Score])
When I add it to my matrix, here's how it looks:
Without using the YTD Score column, it looks like this: 
Also, when is it best to create a new Measure, as opposed to a new Calculated Column? They both seem to do the same thing.
Best regards,
Mark.
Hi Mark,
AVERAGEX won't calculate a YTD value. If you look at the first image you can see the value for YTD Score is the same every month. Also, you can't show the months and only show the YTD at the end. You either remove the months from the column labels, or move the row labels above the values in the column well of the field list.
You might like to review session 10.03 of the Power Pivot course for the Time Intelligence functions on how to calculate YTD figures.
To answer your measures vs calculated columns question; calculated columns are only necessary when you have to reference values at a row level of detail. e.g. if you have a column for units and a column for unit price and you want to calculate the sale value for each row (i.e. units x unit price). If you wrote a measure for this you would effectively be summing the units and then summing the unit prices before multiplying the totals by one another and it would give you nonsense. You might like to watch session 5.07 of the Power Pivot course for a refresher on when to use Calculated Columns.
In your example here the correct solution is a measure. Measures are also more efficient for Excel to calculate.
Mynda
Hi Mark,
Just thought...you could create a 'Set' for your YTD figures as described in video 11.01 of the Power Pivot course.
Mynda
