topic Re: Calculate Hierarchy of Averages within Category in DAX Commands and Tips

Calculate Hierarchy of Averages within Category

souvik900766 — Mon, 11 Jan 2021 11:44:32 GMT

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 !

Re: Calculate Hierarchy of Averages within Category

amitchandak — Mon, 11 Jan 2021 12:18:42 GMT

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)

Re: Calculate Hierarchy of Averages within Category

souvik900766 — Mon, 11 Jan 2021 13:24:01 GMT

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

Re: Calculate Hierarchy of Averages within Category

mahoneypat — Mon, 11 Jan 2021 22:58:37 GMT

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

Pat

Re: Calculate Hierarchy of Averages within Category

Ashish_Mathur — Tue, 12 Jan 2021 01:54:47 GMT

Hi,

You may download my PBI file from here.

Hope this helps.