Forum Discussion
Burndown with visual level filter
Hi All,
I am trying to create a burndown of remaining tasks left, based on the task end date, which falls into an Agile sprint number. Each task is also assigned to a team. Sample data below:
| Team | Sprint | Key |
| 1 | 120 | ABC-1 |
| 2 | 125 | ABC-2 |
| 3 | 122 | ABC-3 |
| 4 | 120 | ABC-4 |
| 2 | 121 | ABC-5 |
| 3 | 123 | ABC-6 |
| 4 | 127 | ABC-7 |
| 1 | 129 | ABC-8 |
| 2 | 124 | ABC-9 |
| 3 | 126 | ABC-10 |
| 2 | 122 | ABC-11 |
| 1 | 120 | ABC-12 |
| 4 | 125 | ABC-13 |
| 4 | 126 | ABC-14 |
| 4 | 127 | ABC-15 |
| 2 | 128 | ABC-16 |
| 1 | 123 | ABC-17 |
| 3 | 122 | ABC-18 |
| 2 | 124 | ABC-19 |
| 1 | 125 | ABC-20 |
This is the best I have come up with:
burndown = CALCULATE(COUNT(tasks[Key]), FILTER(ALL(tasks), tasks[sprint]>=MIN(tasks[sprint])))
Here I encounter two problems:
- It does not take into account visual filters, so I can't easily filter by team, unless I include the specific team in the calculated measure. I would rather just have 1 measure rather than create 4.
- There may be sprints where there are no tasks due in that particular iteration, so the column for that sprint shows as empty. I want it to still show a sum of remaining tasks left regardless.
Here is what happens when there is no filter of team - works fine
Here is what happens when I change the visual filter to team 1. Shows as 20 tasks for team 1, and missing columns of data
And here is what I would ideally like it to look like, as an example for Team 1
Any assistance would be appreciated!
Hi aslee
This small tweak might help. And if you want a bar chart for each sprint, you could have a separate table for that. Let me know if it looks close.
burndown = CALCULATE( COUNT(tasks[Key]), FILTER( ALLEXCEPT('Tasks','Tasks'[Team]), tasks[sprint]>=MIN(tasks[sprint]) ) )
4 Replies
- Phil_SeamarkMicrosoft Employee
Hi aslee
This small tweak might help. And if you want a bar chart for each sprint, you could have a separate table for that. Let me know if it looks close.
burndown = CALCULATE( COUNT(tasks[Key]), FILTER( ALLEXCEPT('Tasks','Tasks'[Team]), tasks[sprint]>=MIN(tasks[sprint]) ) )- asleeHelper I
Thanks Phil_Seamark - that definitely looks better!
Could you clarify what you mean by a separate table?
- Phil_SeamarkMicrosoft Employee
Hi aslee,
Just thinking the chart you get with my measure doesn't match perfectly your desired chart. I can have a look at this a little later tonight to flesh out a measure that means the chart looks closer to your desired result.