Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robpiu
New Member

Help with a measure weighted and non weighted averages

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 %

 

 

 

 

1 REPLY 1
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2020-12-08 132349.png

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] )
)

Screenshot 2020-12-08 132554.png

 

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors