Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Group Average Hierarchy

Hey All,

 

I have a hierarchy that goes Division - Group - Sub Group. I'm trying to return a division average that can apply to both the group and subgroup. I've tried variations on the formula below but the best I can return is a division rate that only applies at the Group level but not the subgroup. 

 

Division Rate = 

if(ISFILTERED(dimGroup[Group]),
AVERAGEX(
	KEEPFILTERS(VALUES(dimDivision[Division])),
CALCULATE([Rate],all(dimGroup[Group]))), "FALSE")

The tables below show what I'm returning - you'll see the division rate in the group table matches the respective divisions in the division table and I want the same to apply to the sub grouping. Thanks in advance!

 

Need Help

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    Have you tied replacing dimGroup[Group] with dimSubGroup[SubGroup] in above measure formula to test if it can return expected result?

     

    Regards,

    Yuliana Gu