Forum Discussion

PSan1979's avatar
PSan1979
Helper II
5 years ago

Creating hierarchy and populate value accordingly

Hi Experts,

 

I am facing this issue for couple of days. I try to create like below. I have created the hirerachy Header is Score(my case Sales Pairs)

sub header is ACT,FST,%AF(my case EST,xxx,xxx). I want to create a measure compare with hirerachy header and subheader and populate the value accordingly. 

 

Measure =
VAR __Value1 = MAX('Custom Hierarchy'[Value1])
VAR __Value2 = MAX('Custom Hierarchy'[Value2])
RETURN
SWITCH(
TRUE(),
ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "Sales Pairs",
ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "EST",
VAR _SALESEST = VALUE([EST])
 
RETURN
SUMX(FILTER(DIM_MDSTORE,(DIM_MDSTORE[FootwearTarget]+DIM_MDSTORE[NonFootwearTarget]) = _SALESEST) ,[EST]),
ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "EST",[EST],
ISINSCOPE('Custom Hierarchy'[Value1]) && __Value1 <> "Other",
VAR _SALESEST = VALUE([EST])
RETURN
SUMX(FILTER(DIM_MDSTORE,(DIM_MDSTORE[FootwearTarget]+DIM_MDSTORE[NonFootwearTarget]) = _SALESEST) ,[EST]),
ISINSCOPE('Custom Hierarchy'[Value1]) && __Value1 <> "Other",
BLANK(),
BLANK()
)
)