Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 % |
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.
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! |
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |