Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to create an Average Measure which will retain the same value regardless of the context filter applied in the Visualisation Tables. For some reason, AllExcept doesn't seem to work on this particular measure I have created. I feel like the calculation is simple (especially on excel) but I seem to get stuck getting the correct DAX formula to the derive average. I have made it work on one visualization but can't get it to work on another.
Measure: Peer Average Excluding Highest CDS) where Bank Product <> NA, Bank Type <> Child
1. Peer Average exc highest
( SUM(BANK A:E)-BANK C ) / ( COUNT(BANK A:E) - 1 )
= 34
2. Relative to Peer Average
Bank CDS / Peer Average exc highest
Bank A: 26/34
= .76
Look Up Table | ||
Bank | Type | Product |
A | Parent | Derivative |
B | Parent | Cash |
C | Parent | Derivative |
D | Parent | Cash |
E | Parent | Derivative |
F | Child | NA |
G | Child | NA |
FACT TABLE
Date | Bank | CDS | Peer Average exc highest (MEASURE)
| Relative to Peer Ave (MEASURE) |
1-Nov | A | 26 | 34 | 0.764706 |
1-Nov | B | 44 | 34 | 1.294118 |
1-Nov | C | 67 | 34 | 1.970588 |
1-Nov | D | 40 | 34 | 1.176471 |
1-Nov | E | 26 | 34 | 0.764706 |
1-Nov | F | 10 | ||
1-Nov | G | 5 | ||
2-Nov | A | 54 | 54 | 1.009346 |
2-Nov | B | 71 | 54 | 1.327103 |
2-Nov | C | 57 | 54 | 1.065421 |
2-Nov | D | 60 | 54 | 1.121495 |
2-Nov | E | 43 | 54 | 0.803738 |
2-Nov | F | 50 | ||
2-Nov | G | 26 |
Visualisation 1 - My measure works in this visualisation.
Bank | CDS | Relative to Peer Ave |
A | 26 | 0.764706 |
B | 44 | 1.294118 |
C | 67 | 1.970588 |
D | 40 | 1.176471 |
E | 26 | 0.764706 |
Visualisation 2 - But same measure doesn't work in this.
Bank A
Date | Relative to Peer Ave |
1-Nov | 0.764706 |
2-Nov | 1.009346 |
3-Nov | 0.23 |
4-Nov | 0.456 |
5-Nov | 0.246 |
6-Nov | 1.233 |
7-Nov | 1.7 |
@PowerBIFin , Try like
measure =
calculate( divide(sum(Table[CDS]),calculate(Average(Table[CDS]), filter(Table, allexpcept(Table, Table[CDS] >calculate(min(Table[CDS]), allexpcept(Table, Table[Date])))), allexpcept(Table, Table[Date]))) , values(Table[Date]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |