Help with a measure weighted and non weighted averages

[robpiu]

Hi everybody,

I'm struggling with this measure.

I have N level hierarchy with some statuses to be set as active (Nr. is the number of statuses, Actitve the sum of the active ones).

Each level might have a different number of sons, and I need to show the % of active statuses. So far, pretty easy.

When it takes to group by superior levels, the aggregations must be "Average" of the levels below. Basically I need to make an average of the "Active %".

There is an example below because I'm not sure I have been clear 🙂

				SUM(Active) / SUM(Nr.)
L1	L2	Nr.	Active	Active  %	Non Weighted Average
X	a	100	40	40%	40%
	b	50	40	80%	80%
	c	20	15	75%	75%
	Tot X	170	95	56%	65%	<-- this is the average of a, b, c %
Y	d	3	2	67%	67%
	e	2	1	50%	50%
	Tot Y	1	1	100%	58%
Z	f	6	4	67%	67%
	g	10	9	90%	90%
	Tot Z	10	9	90%	78%
Gran Total		181	105	58%	67%	<-- this is the average of X, Y, Z %

[CNENFRNL]

Hi, @robpiu , you might want to apply this measure to a transformed dataset

CAL PCT = DIVIDE( CALCULATE( SUM( Table1[Value] ), Table1[Type] = "Active"), CALCULATE( SUM( Table1[Value] ), Table1[Type] = "Nr.") )

=============================

Non Weighted Average = 
IF (
    ISINSCOPE ( Table1[L1] ),
    AVERAGEX ( DISTINCT ( Table1[L2] ), [CAL PCT] ),
    AVERAGEX ( DISTINCT ( Table1[L1] ), [CAL PCT] )
)

You might want to refer to the attached file for details.