5 Replies Latest reply: Oct 28, 2010 5:49 PM by John Witherspoon

# Chart automatically averaging out data

Hi everyone,

Still new at this...Not sure why the graph is automatically taking the average of my data. Is there a way to chart only the data that is in the table even if calculations are being done behind the scenes. The straight table says one thing the chart is different.

The only expression I have is "% of total cost by BU: Sum(COST)/Sum(ALL <"Year","Business Unit"> COST) "

First I have no year selected. When I chart this it takes the average of the years and displays the activity along the "x" and percentage on the "y." So for activity 1 the average is around 14.72% and Activity 2 is .02%

 Activity Business Unit Year % of Total Cost by BU Activity 1 BU1 2008 14.46% Activity 1 BU1 2007 14.94% Activity 2 BU1 2008 0.02% Activity 2 BU1 2007 0.02%

Next I select 2007. Table automatically changes to below.

 Activity Business Unit Year % of Total Cost by BU Activity 1 BU1 2007 14.94% Activity 2 BU1 2007 0.02%

But when I chart this graph it shows the percentage of Activity 1 to be 8.14%. It is counting the sum of costs for 2008 as zero and than taking the average bringing it down to 8.14%. If I select 2007,I want it to chart just 2007 without taking an average. The straight table shows the data I want but the chart is the average with 2008 as zero.

How can I force it to chart 14.94% or am I doing this completely wrong. Thanks!

• ###### Chart automatically averaging out data

Ideally a trend chart(bar/line) should not have more than 2 dimensions.

That is why you might not get the desired.

• ###### Chart automatically averaging out data

Thanks Rocky. I included year to help make it easier to explain but even with just two dimensions (activity, business unit) it still has the same problem.

Is there a different way to write the expression? I need to know the percentage of (activity dollars)/(total dollars in the business unit). This needs to the case for all dollars in 2007 and 2008 or by each specific year if the user wants to see it individually. The chart doesn't need to show they year because users will know what year(s) has been selected.

• ###### Chart automatically averaging out data

I'm not exactly following what you want, but I believe you need to use TOTAL instead of ALL. ALL ignores selections.

• ###### Chart automatically averaging out data

Thanks John.

TOTAL doesn't work because it dispalys the percentage in terms of the selection and I want it to give the percentage regardless of what drill-down or selections are made.

This is an example:

1. Raw data

 Activity BU Year Cost Activity 1 BU1 2008 10 Activity 1 BU1 2007 30 Activity 2 BU1 2008 25 Activity 2 BU1 2007 40 Activity 3 BU1 2008 10 Activity 3 BU1 2007 30 Activity 1 BU2 2008 10 Activity 1 BU2 2007 20 Activity 2 BU2 2008 30 Activity 2 BU2 2007 40 Activity 3 BU2 2008 80 Activity 3 BU2 2007 30

If the user selects no year the table should show

 Activity BU Year Cost BU Total % of Total Cost by BU Activity 1 BU1 2007 and 2008 40 145 27.59% Activity 2 BU1 2007 and 2008 65 145 44.83% Activity 3 BU1 2007 and 2008 40 145 27.59% 100.00% Activity 1 BU2 2007 and 2008 30 210 14.29% Activity 2 BU2 2007 and 2008 70 210 33.33% Activity 3 BU2 2007 and 2008 110 210 52.38% 100.00%

If the user selects 2007 the table should be

 Activity BU Year Cost BU Total % of Total Cost by BU Activity 1 BU1 2007 30 100 30.00% Activity 2 BU1 2007 40 100 40.00% Activity 3 BU1 2007 30 100 30.00% 100.00% Activity 1 BU2 2007 20 90 22.22% Activity 2 BU2 2007 40 90 44.44% Activity 3 BU2 2007 30 90 33.33% 100.00%

These tables are working with the current expression. % of Total: Sum(COST)/Sum(ALL <"Year","Business Unit"> COST)

However when I create the chart off of 2007 (the above table) it automatically graphs the bottom data because the cost of 2008 is zero but is included in the BU Total.

 Activity BU Year Cost BU Total % of Total Cost by BU Activity 1 BU1 2007 30 145 20.69% Activity 2 BU1 2007 40 145 27.59% Activity 3 BU1 2007 30 145 20.69% 68.97% Activity 1 BU2 2007 20 210 9.52% Activity 2 BU2 2007 40 210 19.05% Activity 3 BU2 2007 30 210 14.29% 42.86%

Appreciate any help. Thanks.

• ###### Chart automatically averaging out data

Still confused. You seem to say you want to use ALL because you "want it to give the percentage regardless of what drill-down or selections are made." But then you seem to complain about the bottom chart "because the cost of 2008... is included in the BU Total". It seems you instead want the first two charts, not the third chart. And you can generate the first two charts by using TOTAL instead of ALL. Specifically:

Cost = sum(Cost)
BU Total = sum(TOTAL <BU> Cost)
% of Total Cost by BU = Cost/"BU Total"

See attached. Is that not what you want?