My report is built on a transformer cube in which I have let transformer create the date dimension. I am getting different results with a calculated measure when I nest the transformer created YTD level and my calculated YTD set.
My measure is, for example, Revenue / Qty.
My report should contain the Measure with YTD and Prior YTD nested under the measure.
I started with a data item YTD_Months_SelectedMonth: periodsToDate([Sales Analysis].[Date].[Date].[Year], [selectedMonth])
That data item correctly returns the set of month members. When the data item is added to the report it creates a column for each month. I want a single column.
So I created a data item YTD_Avg: average (currentMeasure within set [YTD_Months_SelectedMonth] )
That data item does what it says - it averages the currentMeasure but the currentMeasure is calculated for each month in the set and then averaged - which is the wrong answer.
If I use the YTD level that transformer created I get the correct result, that is the total revenue for YTD / total quantity for YTD.
I have determined that I can create individual data items that calculate the total ... within set ... and get the correct results.
total ([Revenue] within set ( [PYTD_Months] ) ) / total ( [Quantity] within set ( [PYTD_Months] ) )
I created a Query Calculation as a Member Set using the Dimension Date and the Hierarchy YTD ... looks very much like the transformer created YTD level but the member set returns a column for each month.
How can I create a calculated "something" that behaves the YTD level?
Thanks for your help.