Calculate Hierarchy of Averages within Category

[souvik900766]

Hi !
I want to find out the Average of the Averages !
When I select T10 from T10/NT10(My first slicer in dashboard), want to see the averages within T10.
Average of the Sub Categories will be the average of the respective products within T10,
Average of the  Categories will be the average of the respective Sub Categories within T10,
Average of T10 will be average of respective Categories within T10.
My Data:-

Category	Sub Category	Product	T10/NT10	Value
A	A1	P1	T10	3
A	A1	P2	T10	4
A	A1	P3	T10	5
A	A2	P4	T10	1
B	B3	P9	T10	1
B	B3	P11	T10	5
B	B1	P13	T10	4
B	B1	P14	T10	6
B	B1	P15	T10	7
A	A2	P5	NT10	4
A	A2	P6	NT10	5
A	A3	P7	NT10	3
A	A3	P8	NT10	6
B	B3	P10	NT10	4
B	B3	P12	NT10	1

I have used a formula like this and My values for Category and Sub actegory is coming correct but not for T10/Nt10:-
Avg Value = AVERAGEX(SUMMARIZE(T1, T1[Category], T1[Sub Category],T1[T10/NT10), CALCULATE(AVERAGE(T1[Value])))
T10 will be average of Category A&B that falls within T10 and same for NT10

The desired answer I am looking for:-

Thanks in advance !

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mahoneypat]

What are the #s you expect to see in the red highlighted cells?

Pat

[amitchandak]

@souvik900766 , Try if this measure can work

New measure =
var _tab = SUMMARIZE(T1, T1[Category], T1[Sub Category],"_1", CALCULATE(AVERAGE(T1[Value])))
return
AVERAGEx(summarize(_tab, _tab[Category], "_2", AVERAGEx(Table,_1)),_2)

[souvik900766]

Hi !
I am finding an issue in the return:- cant find _tab[Category] and _1,_2 is not coming.