November 6, 2009 at 3:59 pm
I have a dimension called position (a numeric field), which is sometimes used to cut by.
But in this situation, I want the average position for a time period.
Is it possible to use the average function on a dimension field (as opposed to a measure)?
If so, please help.
For Example, this pulls data:
WITH
MEMBER [Measures].[Position] as [Dim Position].[Dim Position].currentmember.name
MEMBER [Measures].[Avg Position] as AVG([Hotel].[Hotel Key].currentmember,[Measures].[Position])
SELECT
({
[Measures].[RecordCount],
[Measures].[Position],
[Measures].[Avg Position]
}) ON COLUMNS ,
filter((
[Date].[Calendar].[Date].&[2009-10-16T00:00:00]
,nonempty([Hotel].[Hotel Key].[Hotel Key])
,[Position].[Dim Position].children
)
,[Measures].[RecordCount]>0)
ON ROWS
FROM [Lodging]
BUT it gives the average position for each position - which, obviously, is not helpful
I want the average position for each hotel.
I want to remove the ,[Position].[Dim Position].children line from Rows, but then the MDX doesn't work.
Any suggestions greatly appreciated.
Thank you, Megan
March 16, 2010 at 5:21 pm
still no ideas on how to do this?? 😉
March 25, 2010 at 9:58 am
You could always solve this in the ETL phase or in the Data View by exposing the Position as both a numeric value and as a foreign key to the Position dimension.
That is probably a clue as to the MDX path as well.
I'm getting back into MDX and BI and looking for problems to solve. If you will translate your problem to AdventureWorks, I'll see if I can find a solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply