HI.. I have calculated measure which I am trying to display on a trend line graph. There are two graphs that display 8 weeks historical attrition and 8 week pipline attrition respectively. For simplicity lets say the fomula is sum(week exits)/sum (week head count).
Now..for the pipeline graph, I need to use the the (static) head count value of the 8th week (from historical attr) as denominator, meaning sum(future exits)/sum ( week head count (latest week)). The issue here is when I do so..the result is correct when i use a card visual ..however when I use the graph the same further filters/splits the denominator in context of the future weeks, thus generating an incorrect denominator..
The ask here is can how can I keep the denominator in the measure static (like freezing value in excel) while still allowing the numerator to fitler itself basis the X axis of the graph...at the same time the denominator should respond to the slicers and other filters on the page.
Illustration below:
ask: the pipeline attrition measure should use 170 as denomiator (this is been achieved) however it is splitting 170 in context on week 9 and later thus ruining the denominator.
| Historical | | | |
| | AC | HC | Measure
(AC/HC) |
| week 1 | 10 | 100 | 10% |
| week 2 | 20 | 110 | 18% |
| week 3 | 30 | 120 | 25% |
| week 4 | 40 | 130 | 31% |
| week 5 | 50 | 140 | 36% |
| week 6 | 60 | 150 | 40% |
| week 7 | 70 | 160 | 44% |
| week 8 | 80 | 170 | 47% |
| | | | |
| Pipeline | | | |
| week 9 | 10 | 170 | 6% |
| week 10 | 20 | 170 | 12% |
| week 11 | 30 | 170 | 18% |
| week 12 | 40 | 170 | 24% |
| week 13 | 50 | 170 | 29% |
| week 14 | 60 | 170 | 35% |
| week 15 | 70 | 170 | 41% |
| week 16 | 80 | 170 | 47% |
thanks in advance
