Percentage with Series in Bar Chart

Vane

I am trying with no success to calculate the Utilization% of our employees using a bar chart through time. I have a bar chart with the counts of days per period of time and with the requested series that are the utilization types. Formula: SUM(StaffedDays)/ SUM(AvailableDays). This is the counts bar chart that is working fine.

Then when replacing the Sum(Amount) that is showing the correct counts per Utilization Type, when replacing with the Utilization % beastmode the chart is not showing what is expected.

What I need is to see the Utilization % by Type increase up to 100% or more if that is the case. Something like this.

I tried other options like the data labe macros with percent of category or of total, but that is not what I need. I need the correct % and how is affected if filtering adding or removing those types of utilization (series).

Any help would be so much appreciated. 😉

ColemenWilson

Try removing the SUM of Amount from your sort.

Vane

Thanks! I did but I think probably is something with the data format of the dataset. hmmm. This is what I am getting, now. This is filtering for this month. It is creating multiple bars, not sure why. The date field is the week's start date. An employee has the same week start dates with different types of utilization. And also leaves out some categories (series) not showing.

ColemenWilson

Because you still have Amount in your sorting. You need to remove it from your sorting.

Vane

Thank you for your reply :).

I removed it too, but with no success. To give more context. These are the counts of assignments for 2/2024, where you see counts for all values in the series.

Now when I put the Utilization % value on the Y axis. It only shows two types of the series and the calculation is very off. The overall utilization is 86% and the green type is 77% of that 86%. Look what is showing:

I know also that I am working on one of the assignment types with a weird business rule. Utilization Rate = Staffed Days/Available Days. For the blue type (series), we don't have Available Days because they are Resources not yet assigned, like pending to assign that are not real people. StaffedDays/0=error. So I don't know if that is the problem…

@ColemenWilson

ColemenWilson

Can you share a data sample and your utilization beastmode?

Vane

@ColemenWilson here it is:

DavidChurchman

1. Yes, if you have 0 available days in the denominator, you will get NA values for that, which is why it would not show that category on the graph.
2. You're adding percents by category, so even if your overall utilization rate was 86%, if your utilization of green was 97% and your utilization of orange was 93%, then the stacked bar will add that to 97+93=190%. (Your utilization of the other categories looks like 0%, so 86% seems like it could be right). Instead of stacking your utilization rates (which doesn't really make sense),
   1. You might want to group them instead of stack. Or do grouped bar+symbol with the overall.
   2. If you remove the series, do you get the correct overall utilization rate per month?
   3. Instead of creating a new graph, you might just put the utilization rate in a tool tip or your original and add it to the data label (since the group sizes are so different).

Vane

@DavidChurchman great advice and explanation. Thank you for your time on this. At this point, I think that mathematically is impossible the way they want me to present this, is not possible. If it is zero for available it is not possible to calculate utilization for that "possible utilization" type.

They were looking to dynamically see how those "possible utilizations" affect the overall utilization to determine if we need to hire or reassign others. The need is valid but mathematically without losing granularity and dynamic filtering is going to be difficult and maybe not possible at all.

I'll be trying to think of other ways to help. Thank you so much!!!