CODEWITHSUNDEEP
ssasmdxdata-warehousecubessas-2008
Jeffrey Bane
Jeffrey Bane

Reputation: 592

MDX / SSAS - Need flexible percent of parent measure

I’ve been searching for hours on this and I’m just not adept enough at MDX to know if I’ve come across the solution yet. Everything I tried has not worked. The situation is, I need a “percent of parent” measure. In this example, the only thing I have gotten to work, each product code attribute displayed shows it’s sales as a percentage of every other product code attribute displayed:

([Product].[Product Code].CURRENTMEMBER,
[Measures].[Sales]) / ([Product].[Product Code].CURRENTMEMBER.PARENT,
[Measures].[Sales])

The problem is when the users start dragging other dimensions in or other attributes not in that hierarchy, or don’t use the product code attribute, it stops working and I get #NUM! values. Is there anyway to do a better percent of parent measure that’s more flexible? I really need something like a “percent of what’s visible” measure, ideally not dependent on a single dimension.

Upvotes: 2

Views: 1178

Answers (1)

Tom Martens
Tom Martens

Reputation: 776

unfortunately I can't provide a fully working example due to the lack of my laptop during my vacation :-)

What you're looking for can be achieved with the MDX functions AXIS and ITEM. Within this book

http://www.amazon.com/Microsoft-Server-Analysis-Services-Cookbook/dp/1849681309/ref=sr_1_1?ie=UTF8&qid=1372255356&sr=8-1&keywords=tomislav+piasevoli

there is a dedicated chapter about "Context-Aware Calculations"

Hope this helps somehow

Tom

Upvotes: 2

Related Questions