Jump to content
We've recently updated our Privacy Statement, available here. ×

How to prevent the % column from summing and calculating using the correct figures

Katie Langford

Recommended Posts

Here's an example of the data I have:

Team         Shift      Direct     Indirect      %Utilized

Cleaning      Shift B   150.25      10.5          93%

Cleaning      DAY1     315        103        75%

The utilized column is calculated by Direct/(Direct+Indirect). I have placed the data in a cross table and when both filters are selected it sums the %utilized to 168% where I'd like it to show 80%. So it would be 93% when Shift B is selected, 75% when DAY1 is selected and 80% when both is selected.

Link to comment
Share on other sites

I don't think a cross table can do this, as this seems calculated row by row and you cannot add percentages.

I would suggest using a KPI chart.

For instance you can set it up so that the Value (y-axis) is defined as:

Sum([Direct]) / (Sum([Direct]) + Sum([indirect])) as [Percentage]

 (formatting it as a percentage with e.g. zero decimal figures)

and it would react to filters and look like the snapshot.

Link to comment
Share on other sites

Hi Gaia,

Many thanks for your response. I'm not sure if the KPI chart would be the best option as I'll need the Available Utilization for each day. I've attached a screenshot of the dashboard which will provide more detail:

Shift A:


image(1).thumb.png.eab4ebf79ffe7d99e8c69cf82c77206f.pngShift A and Days:

image(2).thumb.png.194724580eb41ec0cc7908fc789ea16d.pngI'd like Shift A and Days filter to show 90% (606.5/(606.5+68.5))

Thanks in advance

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...