# Progress Bar Dependent on Status of Tasks

Hi! If anyone could help me with this, that would be amazing and well appreciated.

I would like my progress bar to reflect the status changes from a subtasks table.

The subtasks table has statuses of Not Started, In Progress, Complete. I’m wondering what formula I can use. For example, if 3/6 of the subtasks are completed, I would like the progress bar be half colored in or if 1/5 of the subtasks are completed, a little bit of the progress bar to be colored in.

So far, I have used this formula Round((thisRow.Subtasks.Filter(Status=Completed).Count())/(thisRow.Subtasks.Count())*100,1) BUT it only works for when the status is complete and I can’t seem to find a way to do it for the other 3 statuses.

Hi @Kayla_Van !

The thing is that when it comes the whole cycle of a subtask in terms of statuses (going from `Not Started` to `Completed`), each status represent a portion of the progression where :

• `Not Started` would be 1/3 of the whole progression
• `In progress` would be 2/3 of the whole progression
• `Complete` would be 3/3 of the whole progression

So, if you want your progress bar to be precise, you also need to account for this, otherwise, if you have 2 subtasks and both are `In Progress` the progress bar will display `100` which is not very accurate (IMHO)

Going a little bit further with this, you could even say that the statuses :

• `Not Started` ranges from `0/3 <= Not Started < 1/3` of the whole progression
• `In progress` ranges from `1/3 <= In progress < 2/3` of the whole progression
• `Complete` ranges from `2/3 <= Complete < 3/3` of the whole progression

Meaning, there are 2 ways to reflect the status changes of your subtasks as the correction to apply by status can be calculated by using a minimum value where :

• `Not Started` is not accounted for as not needed → so, `0`
(So the progress bar display `0` if all subtasks are `Not Started` yet)
• `In progress` is `1/3` of the whole progression `- 1` → so, `1/2`
(as `Not Started` is excluded, the whole progression cycle can be adjusted too)
• `Complete` is `2/3` of the whole progression `- 1` → so, `2/2`
(Same reason as for `In progress`)

Or a maximum one where …

• `Not Started` is `1/3` of the whole progression but should be excluded by a `Filter()`
• `In progress` is `2/3` of the whole progression
• `Complete` is `3/3` of the whole progression

In the sample you’ll find below, I’ve done both because if the formulas follow the same principle for both progress bars, the correction to apply for each status changes a little bit so the results are a bit different .

But in both cases, if all the subtasks are `Not Started` the progress bars will return `0` and `100` if all the subtasks are `Complete` .

For the first `Progress` bar, I began to add to the `Status` table, a number field to find the position of each status in the table minus 1 (this is the field called `Order`). So the formula looks like this :

``````thisTable.Find(thisRow) - 1
``````

Now, in the progress bar `Progress`, you’ll find this formula :

``````thisRow.Subtasks.Count().WithName(Subtasks_Total,
WithName(Status.Count() - 1,Statuses_Total,
Status.ForEach(
CurrentValue.WithName(Status,
WithName(Status.Order / Statuses_Total,Correction,
Round(
,1)
)
)
)
).Sum()
)
)
``````

And what it does is :

1. `thisRow.Subtasks.Count()` : Counts the total of `Subtasks` which returns a number I store in `Subtasks_Total` using WithName()

2. `Status.Count() - 1` counts the total of status (of rows) in my table `Status` which is then stored in `Statuses_Total` using another `WithName()`

3. I then take the table `Status` and ForEach() status where each specific status (row) is represented `CurrentValue`, I give the name `Status` using `WithName()`

1. `Status.Order / Statuses_Total` : For each status, I calculate the correction to apply/the proportion to keep of each status when compared to the whole progression cycle (using the value in the field `Order` in the `Status` table) and this value is stored within `Correction` (which is a list of numbers, as we’re in for each)
2. `thisRow.Subtasks.Filter(Status = Status).Count()`: For each status, I count total of subtasks per status (which also returns a list of numbers) and stores this value within `Subtasks_per_Status`
3. `Round( ((Subtasks_per_Status * Correction)/Subtasks_Total) * 100 ,1)` : I finally calculates the rounded and corrected proportion of subtasks per status as a list of numbers (as we’re still within `ForEach()`) where the first one should be `0` (as the correction applied is `0`, so whatever the amount of `Not Started` subtasks, it will be `0`), the second one is the proportion of `In Progress` subtasks and the last one, the `Complete` subtasks.
4. All that’s left, is to `Sum()` the values in the list of numbers

I’m sorry … It’s getting quite late here and it’s not easy to explain

The second progress bar (`[Progress 2]`) in the sample follows the same pattern with few exceptions :

1. I’ve used the field `[Order 2]` to determine the correction to apply and I’ve used `Status.Count()` for the `Statuses_Total` (instead of `Status.Count() - 1`)
2. I’ve excluded the status `Not Started` from the whole calculation (as only the others matter here) by applying the `ForEach()` on `Status.Filter(CurrentValue != [Not started])`

I’m really sorry for the length … But I hope this helps a little

1 Like

Hi @Kayla_Van !

What do you mean by “do it for the 3 others” ?

Hi! I would like the filter to also take into account all the status. Right now, the formula I have only takes into account the “Completed” status, but I can’t get it to work for the other three statuses. Does that help? TY!

Hi @Kayla_Van !

You can use `Contains()` in your `Filter()` instead of `=` .

``````thisRow.Subtasks.Filter(Status.Contains([In Progress], Complete).Count()
``````
``````Round(
This would give the total of subtasks with the status `[In Progress]` and `Complete`
Which will work as long as all your subtasks are not all `[In Progress]`, as in this case, the progress bar will also return 100 (as `[In Progress]` and `Complete` have the same “weight” in this case)