Forum Discussion
Calculating Average Days to Fulfill a Position
Hi all,
Trying to work out a measure to calculate the number of days it takes to fill an open work position. Some positions may be posted multiple times (general labour, for example).
I've got a source spreadsheet that tracks the position title, the open date and even the number of days from when the position is open to when it is filled. I'm having trouble getting the average time to fill calculated, though.
Sample Table:
Position Days To Fill
Electrician 4
Electrician 68
Millwright 5
Millwright 40
Plumber 10
Electrician 15
So, what I'd like to see as a result is that Electrician took us 29 days to fill (4+68+14/3 positions). I'm using Column and Line visual. Columns are the position, lines are Days to Fill set to Average. According to the data values displayed, it took us an average of 83 days to fill the Electrician position.
I tried creating a measure that would count the positions and then average the days, but it doesn't seem to calculate properly as most values return simply a 0.
Help! 🙂
Anonymous with the given dataset, you are looking at this
3 Replies
smpa01Community Champion
Anonymous use this measure
measure = calculate(average(tbl[days to fill]),allexcept(tbl,tbl[Position]))Now apparently it took us only 2 days to fill the Electrician role.
I change the visual to just a table to see what is happening. If Include Position, Cycle Time and the new Measure, by default the Cycle column is the Sum of all the days. If I change it to Average, it gives me the same results as your measure.
It isn't just the one role giving us incorrect information, it is every position. I'm just not certain what data I'm missing or if there's some other piece of data that should be in the calculation.
- smpa01
Anonymous with the given dataset, you are looking at this