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

[Katie Langford]

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.

[Gaia Paolini]

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.

[Katie Langford]

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:

Days:

Shift A and Days:

I'd like Shift A and Days filter to show 90% (606.5/(606.5+68.5))

Thanks in advance

[Gaia Paolini]

without a sample dxp I don't think I am able to help further. I think the main problem you are facing is that you cannot add fractions with different denominators. One possible workaround would be to have a cross table with the detail and one with the summary.