Forum Discussion
Advocate IAverage Measure Unaffected by Context Filter
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 |
1 Reply
- amitchandak
Super User
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]))
