MDX: Calculating avg action time and change over time, for top 5 actions

Question

I have an "Actions" cube. The dimensions are "time" and "action ID" and the measurements are "number of actions" and "total time" and a calculated measurement "average action time". I am trying to calculate the top 5 actions by avg time, and the show the change from the previous day. I can do this in two separate queries: SELECT {[Measures].[Avg Action Time]} ON COLUMNS, NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS FROM Actions WHERE [Time].[2005].[1];

and: WITH MEMBER [Measures].[Change] AS ([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]), FORMAT_STRING = 'Percent' SELECT [Measures].[Change] on COLUMNS, NON EMPTY [Time].[2005].[1].children on ROWS FROM [Actions];

But I Can't figure out how to combine them into one MDX Query. I tried: WITH MEMBER [Measures].[Change] AS ([Time].CurrentMember, [Action ID].CurrentMember, [Measures].[Avg Action Time]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Action ID].CurrentMember, [Measures].[Avg Action Time]), FORMAT_STRING = 'Percent' SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS, NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS FROM Actions WHERE [Time].[2005].[1];

but change percentage is always infinity, so it clearly isn't calculating the right thing. What is the correct query?

Answer 1

The problem was that I didn't have enough data in my fact table. This query works for me for a different time range.

WITH MEMBER [Measures].[Change] AS ([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]), FORMAT_STRING = 'Percent' SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS, NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS FROM Actions WHERE [Time].[2005].[1].[2];