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

    Chart automatically averaging out data

    tozaki11

      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%

       

      ActivityBusiness UnitYear% of Total Cost by BU
      Activity 1BU1200814.46%
      Activity 1BU1200714.94%
      Activity 2BU120080.02%
      Activity 2BU120070.02%


      Next I select 2007. Table automatically changes to below.

       

      ActivityBusiness UnitYear% of Total Cost by BU
      Activity 1BU1200714.94%
      Activity 2BU120070.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
          Rakesh Boorgu

          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
              tozaki11

              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
                  John Witherspoon

                  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
                      tozaki11

                      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

                       

                      ActivityBUYearCost
                      Activity 1BU1200810
                      Activity 1BU1200730
                      Activity 2BU1200825
                      Activity 2BU1200740
                      Activity 3BU1200810
                      Activity 3BU1200730
                      Activity 1BU2200810
                      Activity 1BU2200720
                      Activity 2BU2200830
                      Activity 2BU2200740
                      Activity 3BU2200880
                      Activity 3BU2200730


                      If the user selects no year the table should show

                       

                      ActivityBUYearCostBU Total% of Total Cost by BU
                      Activity 1BU12007 and 20084014527.59%
                      Activity 2BU12007 and 20086514544.83%
                      Activity 3BU12007 and 20084014527.59%100.00%
                      Activity 1BU22007 and 20083021014.29%
                      Activity 2BU22007 and 20087021033.33%
                      Activity 3BU22007 and 200811021052.38%100.00%


                      If the user selects 2007 the table should be

                       

                      ActivityBUYearCostBU Total% of Total Cost by BU
                      Activity 1BU120073010030.00%
                      Activity 2BU120074010040.00%
                      Activity 3BU120073010030.00%100.00%
                      Activity 1BU22007209022.22%
                      Activity 2BU22007409044.44%
                      Activity 3BU22007309033.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.

                       

                      ActivityBUYearCostBU Total% of Total Cost by BU
                      Activity 1BU120073014520.69%
                      Activity 2BU120074014527.59%
                      Activity 3BU120073014520.69%68.97%
                      Activity 1BU22007202109.52%
                      Activity 2BU220074021019.05%
                      Activity 3BU220073021014.29%42.86%


                      Appreciate any help. Thanks.

                       

                        • Chart automatically averaging out data
                          John Witherspoon

                          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?