Group Average Hierarchy

[Anonymous]

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!

[v-yulgu-msft]

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