Is there a way to weight a report based on a value?

meganellis07

We track tasks with smartsheets and we have an effort level column that is meant to help demonstrate (via a report) what workload across a department looks like. We want to be able to create a report that weights projects with a '1 effort level' smaller than a '4 effort level'.

Any suggestions on how we could do this?

Will Jeffords

Hi @meganellis07 , how are you determining the "effort level"? Is someone designating that or does it need to be calculated somehow? One part of answering this is likely to use the "symbol" column type in your assets (there are multiple ways to indicate 1-4 rankings with that. Creating a uniform column across your different sheets will allow you to point multiple sheets to consolidating report(s) where you will be able to sort order and group according to the 1-4 ranking (whichever symbol scheme you use). Please describe the mechanics of the effort determination and we will likely be able to suggest multiple solutions!

Best,

Will

meganellis07

Hi Will,

So it's simply a text column that asks people to select 1-4 for their effort level. We have that defined in our policies (1-4 hours = 1, 4-10 hours = 2, 10-40 = 3 and then 40+ = 4.) although I do wish they were bucketed in a more uniform manner.

Today people fill this out and then it reports into a separate sheet that pulls a simple 'count if'

=COUNTIFS({Quarter}, $Name$1, {Level}, [Level 1]$1, {Owner}, HAS(@cell, $Resource@row), {Spend Management Tracker FY25 Range 1}, ="Project")

This then is routed into a bar chart (see image attached). The issue is that the chart that is being shown doesn't weight data in any way. We want the chart to weight level 4 projects as a higher amount of time so that you can actually see if someone is at capacity and accurately compare employee workload to help determine where new projects should fall.

Jgorsich

That type of weighting is often used when trying to prioritize tasks as it simply allows bucketing and simplifying - you see it a lot in Agile software development, for example.

For figuring out someone's capacity you would either want to jump back to just having them estimate hours and then summing them OR you'd want to make a seperate formula that determines capacity off of those numbers - maybe each 4 would equal 50% of their capacity for the month, each 3 would equal 15% of their capacity, each 2 would equal 3% and each 1 would equal 0.5% and then you'd sum up the capacity. Going that second route means doing the following:

1. estimate hours (a pretty good stand in for capacity already)
2. choose the appropriate bucket
3. use the bucket to estimate capacity

It seems like a bit of needless complication that is going to just cause problems. Having the user choose a bucket (for easy comparison and prioritization) AND also just estimate their own capacity devoted to each line seems way easier.