DAX help--> Calculate the AVG Value of a category on the basis of the AVG of the respective SubCate

[souvik900766]

Hi!
I want to calculate the AVERAGE Value of a category on the basis of the AVERAGE Value of the respective sub Category.

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
A	A2	P5	NT10	4
A	A2	P6	NT10	5
A	A3	P7	NT10	3
A	A3	P8	NT10	6
B	B3	P9	T10	1
B	B3	P10	NT10	4
B	B3	P11	T10	5
B	B3	P12	NT10	1
B	B1	P13	T10	4
B	B1	P14	T10	6
B	B1	P15	T10	7

I want the answer to be:-
When I filter T10/NT10 I will get the average of T10/NT10 like:-

T10	4
NT10	3.83

When I filter Individual Product I will get the individual value.
When I filter Sub Category, it will be the average of the Product like:-

A1	4
A2	3.33
A3	4.5
B3	4
B1	5.6

When I filter Category, it will be the average of the SubCategory within that Category like(A1+A2+A3)/3:

A	3.94
B	4.8

So when I filter T10 using a slicer I will get 4, I filter Category A then I will Get 3.94, I filter A1 I will get 4 and when I filter P1 I will get 3.
Kindly help me to formulate the DAX.

Thanks in Advance.

[mahoneypat · Accepted solution]

Please try this measure expression, replacing T1 for your actual table name.

Avg Value = AVERAGEX(SUMMARIZE(T1, T1[Category], T1[Sub Category]), CALCULATE(AVERAGE(T1[Value])))

Regards,

Pat

[littlemojopuppy]

@souvik900766 you want the average of averages?  That's an almost meaningless calculation.  Is this only when filtered by Product?  What happens when if you filter by T10/NT10...is that just a simple average?

[Ashish_Mathur]

Hi,

If you filter on A, the answer should be 3.875 (simple average).  I do not understand your logic.