CODEWITHSUNDEEP
sql-serverssasmdx
Niraj
Niraj

Reputation: 1842

SSAS Calculated measure to show values only for specific dimension values

I have a calculated Measure in my SSAS cube, which I want to calculate only for a group of dimension members and for rest it should be zero. In The following image I want MTDOccupancy dimension to work only for Account Groups for Rooms, rest related to food should show zero for this field. enter image description here

following is the simple MDX which I am using for MTDOccupancy measure

([MTDQuantity]/[MTDAvailableRooms])*100

Upvotes: 0

Views: 4019

Answers (2)

GregGalloway
GregGalloway

Reputation: 11625

Please do it as a scope statement for performance reasons. The following will get evaluated the first time someone connects to the cube vs the LEFT running in every cell at report time: 

Create member CurrentCube.[Measures].[My Calc] 
as null
,format_string="0.0%";

scope(Filter([Account Group].[Account Group].[Account Group].Members,   LEFT([Account Group].[Account Group].currentmember.member_caption,5)="ROOMS")); 
   [Measures].[My Calc] = iif(MTDAvailableRooms=0,null,([MTDQuantity]/[MTDAvailableRooms])); 
end scope;

Upvotes: 0

whytheq
whytheq

Reputation: 35557

You can change the measure to something like the following:

IIF(
    LEFT([Account Group].[Account Group].currentmember.member_caption,5) = "ROOMS"
  ,([MTDQuantity]/[MTDAvailableRooms])*100
  ,NULL
)

I have guessed this dimension/hierarchy combination [Account Group].[Account Group]. ... - you will need to change this to reflect the names used in your cube.

Upvotes: 1

Related Questions