Ps - to clarify things a bit further, unfortunately we can't just calculate average revenue per customer by dividing total revenue by total customers - this is because the avg_revenue_per_customer...

Hi Drew,

We do already have company total revenue as a measure. This isn't actually the measure in question.

The leaf measure we're working on is company average_revenue_per_customer, which may be...

Drew - thanks for simplifying the formula, it does make it clearer.

However we are still at a loss with what the MDX would be to calculate total_revenue i.e..

(CompanyA...

Thanks. It turns out there are a few different ways to do this depending on your situation. If you have the SQL Server Enterprise Ed you can use the...

We worked this out in the end using a nice little recursive call.

member [measures].[lastnonempty] as

case when not isempty(([measures].[price], [time].[time].prevmember))

then ([measures].[price], [time].[time].prevmember)

else [time].[time].prevmember

end